Family Tree Database

[vdtrip]

Hi, I have a project FamilyTree,it's like a Chain Relation Management.But i have no idea that how i will have to manage Database.Can anybody tell me some ideas for database. VD

[Jorgen Andersson]

My first thought was this:

create table family_relatives (
relative_id integer primary key,
spouse references family_relatives,
mother references family_relatives,
father references family_relatives,
-- in case they don't know the exact birthdate
birthyear integer,
birthday date,
-- sadly, not everyone is still with us
deathyear integer,
first_names varchar(100) not null,
last_name varchar(100) not null,
sex char(1) check (sex in ('m','f')),
-- note the use of multi-column check constraints
check ( birthyear is not null or birthday is not null)
);

but it lacks a bit. There's no control that your father is male and that your mother is female. And it doesn't keep track on ex-spouses. So one might want to drop the spouse column and add a partner table instead. Keeping track on that your parents are born before you and not dead at your birth is another one. Or at least the mother, the father might actually be dead at your birth

"When did ignorance become a point of view" - Dilbert

[Mycroft Holmes]

SQL Servers hierarchy ID might be an interating tool to use for this.

Never underestimate the power of human stupidity RAH

[vdtrip]

Hi, How can i use this SQL Server hierarchy ID,please can you explain it. Thanks

[J4amieC]

Google broken where you are?[^]

[Mycroft Holmes]

HierarchyID maitains a parent - child relationhip and may make structuring your data simpler. Do a bit of research in BOL.

Never underestimate the power of human stupidity RAH