数据库设计帮助：无论如何，这是谁的孩子

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)

我正在为我的妻子建立一个联系人管理器程序,作为学习Silverlight 4的有趣练习。它将跟踪人们的地址,电话号码,电子邮件,特殊场合(生日,周年纪念日等)

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.

在设计数据库的过程中,我遇到了一个小路障。她希望能够按家庭对联系人(人)进行分组,因此我创建了一个包含FamilyID和FamilyDescription的Families表,然后将FamilyID添加到表People。

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

现在SELECT * FROM People WHERE FamilyID = 2将返回该特定系列中的每个人。

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.

我非常感谢任何见解,谢谢。

5 个解决方案

#1

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...

PEOPLE.familyid的问题在于它确保一个人只能属于一个家庭。这意味着如果你想区分直系亲属和大家庭,你会看到一个层次结构来关联这些家庭,当它们不一定是分层的时候......

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记录相关联:

PEOPLE_FAMILY_MAP

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

PEOPLE_ID(主键,PEOPLE.id的外键)

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

FAMILY_ID(主键,FAMILY.familyid的外键)

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

如果要显示人与人之间的关系 - 您需要使用关系类型表(type_code,description)和关联关系:

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

PERSON_ID(主键,PEOPLE.id的外键)

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

RELATED_TO_PERSON_ID(主键,PEOPLE.id的外键)

• RELATIONSHIP_TYPE_CODE (primary key, foreign key to RELATIONSHIP_TYPE_CODES.RELATIONSHIP_TYPE_CODE)

RELATIONSHIP_TYPE_CODE(主键,RELATIONSHIP_TYPE_CODES.RELATIONSHIP_TYPE_CODE的外键)

#2

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

难道你没有每个People实体的母亲和父亲列,它引用了另一个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

查看此WPF示例应用程序 - http://www.vertigo.com/familyshow.aspx

#3

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.

例如:祖父母将有-2级,父母将有-1,小孩/青少年将有0,他们的孩子(未来)将有1,宠物可以有2,等等。

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

然后,您可以轻松地生成一个世代的列表。

#4

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 ...)

这里的好处是孩子们也可以成为父母。所以你可以继续定义新的家庭,不要改变先前的关系。还允许您找到一个人所属的所有家庭,或者让您链接以找到相关的家庭...(找到所有家庭,其中familyX.relationtype =儿童出现......)

Just how I would choose to do it.

我将如何选择这样做。

#5

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")

角色可以是以下任何一种(“家庭主管”(又名决策者 - 又名,降压停在那里),“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"

然后对约翰逊先生和夫人来说,你可以拥有“约翰逊”这个家庭。但是如果你想要向所有亲戚发送垃圾邮件,你可以使用“The Johnsons extended”