Database Design Help: Whose kid is this anyway

I am building my wife a Contacts Manager program as a fun exercise in learning Silverlight 4. It will track peoples Addresses, Phone Numbers, Emails, Special Occasions(Birthday, Anniversary, etc)

Along the way I hit a minor road bump while designing the Database. She would like to be able to group Contacts(people) by Family so I created a Families table that has FamilyID and FamilyDescription and then added FamilyID to table People.

Now SELECT * FROM People WHERE FamilyID = 2 would return everyone in that particular family.

I THINK this is an acceptable idea; where I am struggling, though, is how to incorporate children, as in kids. We have a very, very large开发者_运维百科 family so there will be multiple kids per family. Do I just add them to table People, if so, how do I differentiate who the kids are from the adults?

I am very grateful for any insight, thanks.

The problem with the PEOPLE.familyid is that it ensures a person can belong to only one family--ever. Which means if you wanted to differentiate between immediate and extended family, you'd be looking a hierarchical structure to associate the families when they aren't necessarily hierarchical...

The most accommodating solution would be to include a many-to-many table, so you could associate a PEOPLE record to any number of FAMILY records:

PEOPLE_FAMILY_MAP

• PEOPLE_ID (primary key, foreign key to PEOPLE.id)
• FAMILY_ID (primary key, foreign key to FAMILY.familyid)

If you want to show relationships between people - you'd need a relationship type table (type_code, description), and associate relations using:

• PERSON_ID (primary key, foreign key to PEOPLE.id)
• RELATED_TO_PERSON_ID (primary key, foreign key to PEOPLE.id)
• RELATIONSHIP_TYPE_CODE (primary key, foreign key to RELATIONSHIP_TYPE_CODES.RELATIONSHIP_TYPE_CODE)

Wouldn't you have a Mother and Father column per People entity, which referenced another PeopleId?

Children are people too, you know!

Also, what is your definition of a Family? People who live at the same address. Boxing people in to a family sounds difficult, how many generations constitutes a family.

And my idea about a mother and father column per People entity does not take in to account gay couples with children, but we shouldn't over think the problem or you could drive yourself mad.

Check out this WPF sample appliction - http://www.vertigo.com/familyshow.aspx

You can create a column Level in People table and assign the generation to it.

Example: Grandparents will have level -2, parents will have -1, kids/teens will have 0, their kids (future) will have 1, pets can have 2, etc.

Then you can easily generate the list of a family by generations.

I would do some sort of intersection table and maybe assign a field on the relation for "relation type" ... so you could define like aunts and uncles, or mother in law, father in law ... etc. Then also have a family table like you describe. That keeps the number of fields lower, and prevents potential empties or nulls creeping in, at the expense of more rows...

The benefit here is that kids can also be parents later. So you can just keep defining new families and don't change the prior relations. Also allows you to find all the families one person belongs too, or lets you do chaining to find related families ... (find all families where familyX.relationtype = children appear ...)

Just how I would choose to do it.

OK, I'm going to go a different route here.

She wants families because she wants to be able to mass email them spam junk mail of course. So it's not like we need every single family in the families list. Really she will create families and add people to them as needed (just like a mailing list).

So:

Have a families table:

• FamilyId
• Name
• Description

Then just have a mapping table:

• FamilyId
• PersonId
• Role

role can be any of the following ("Head of family" (aka the deciders - aka, the buck stops there), "Peon")

Then for Mr and Mrs Johnson you can have the family "The Johnsons". but if you want to spam all their relatives you use "The Johnsons extended"