Family Tree Database

[hrushi9]

Working on law firm website. Different Lawyers have assigned different cases ( in same law firm).

For that purpose it needs Family Tree implementation to collect clients history.

There is main table persons

personID   primary key AUTO INCREMENT 
firstName
lastName
sex
birthDate
birthPlace
deathDate
deathPlace

parents, spouse, childre all are stored in same 'persons' table as person. Everyone is treated as independent person. some clients are married twice, some are not married, some are divorced. So need help to maintain records of Marriages.

I need help to create tables to record information regarding parents, spouse info (husband or wife) , children, Married or Remarried, previous spouse , or Divorced(Marriage Date, Divorce Date).

How to collect info of relationship like uncle, aunt, sister, brother, cousin, in-laws (brother-in-law , sister-in-law etc.),-

( I'm  just thinking of this,

1. table relation  

    person1_id
    person2_id
    relationship_type (mother,father,sister,aunt,uncle,in-laws)

2.  table partner  

     person1_id
     person2_id
     relationship_type (husband or wife or  live-in relationship)
     beginDate
     endDate

    Is this okay???
)

#Need Help To Design Database#

[diafol]

Gawd, that's a tough one. WHat would be good is an automatic relationship joiner, e.g. son-in-law to "x", automatically creates son-in-law to "y", but there are so many variables as to why this may be too difficult to be possible. Also there could be a number of different relationships between two individuals. For this reason, perhaps a link table with a composite PK on 3 FKs (id1,id2,rel_id) would be the way to go.

Do you really need all this info? Would not a marriages table and maybe a parents table and dependents table be sufficient? Not sure whether fields in these tables could be related or not, without a lot of manual labour.

Most trees deal with direct relations (up/down and sideways) - not cousins etc. See here for an idea: http://www.databaseanswers.org/data_models/genealogy/

[rproffitt]

I think you need to go back to your system design classes. That is, this is done how now. You get the current process down in your design spec before you write code or build your database schema.

If no one can explain how it's done now. Then that's your first job or task.

[hrushi9]

@diafol thanks. your link is useful. I'll post my databse, when it is complete. so, please come back again with suggestions.

@rproffitt why should I go back to classes?

It's law firm website where police & lawyers are involved. Toooooo many relations are available to keep record & maintain without error. All this databse will be used for case study & it's management.

[rproffitt]

@hrushi9

One of your classes you hopefully took way back when was called something like "System Analysis." You have a problem to automate in your app/program and to do that you look at how it's done today. You take the manual process, document it and then move to the design phase where you think about how to make an app/program that does the same. In that design you will also be designing your data structures.

You explained why you need this, but you may be missing out on the first step. "System design."