Family Tree Database

More than a parent-child tree

A useful family-history model must represent people, marriages, addresses, education, employment, achievements and events without flattening them into one oversized table.

The requirements also account for biological and non-biological parenthood, divorce and remarriage, children across different marriages, multiple names, incomplete work histories and attendance at other family members’ life events.

Relationships made explicit

An enhanced entity-relationship model identifies stable entities and resolves many-to-many relationships through associative structures. Normalization reduces duplication and protects data integrity.

Person sits at the centre of the model, connected recursively through father, mother and spouse relationships and historically through homes, organizations and achievements. Cardinality and participation rules distinguish required biographical data from optional education, work, marriage and event histories.

Thirteen tables with distinct responsibilities

Regular entities become Person, Home, Organization and Achievement tables. Associative history tables resolve many-to-many residence, education, employment and achievement relationships, while recursive parent and spouse tables preserve relationships between people.

Middle names and used names are mapped into separate tables as multivalued attributes. Composite primary keys, unique constraints and foreign-key cascades make duplication and orphaned history records less likely.

• Person · Home · Organization · Achievement
• HomeHistory · EducationHistory · WorkHistory · AchievementHistory
• FatherOf · MotherOf · SpouseHistory
• Person_MiddleName · Person_UsedName

A schema designed against repeated facts

The companion normalization study documents how the initial family records are decomposed into stable entities and relationship tables. Separating people, places, organizations and time-bounded histories reduces update anomalies while keeping biographical facts queryable.

One model, two SQL environments

The schema is implemented for MySQL and Microsoft SQL Server, demonstrating how a conceptual model is translated across related database systems.

Parallel scripts account for dialect differences such as AUTO_INCREMENT versus IDENTITY(1,1), and TIMESTAMPDIFF versus DATEDIFF. Each platform includes database creation, sample insertion, query and teardown scripts.

Family history as answerable questions

Twenty SQL examples test the usefulness of the model through joins, views, updates, deletes, aggregation and unions. They retrieve names and aliases, adults, siblings, parents, adopted children, marriage history, residences, education and work relationships.

The query set moves beyond simple record lookup to questions such as who currently works or studies, who has lived in British Columbia, how many work histories each person has and whether someone later worked for an organization where they studied.