数据库模型代表家庭，家庭和关系 [英] Database Model to Represent Families, Households, and Relationships

问题描述

这里是我的情况。我正在建立一个数据库来跟踪人与家庭之间的关系。通常，一切都与户主相关。

我试图避免这种情况，因为当人们移动（即哥哥终于得到工作，搬出去）或家庭分手（即爸爸和妈妈离婚，家庭现在分为两个，一些孩子留在妈妈，一些有爸爸）。

当模型基于HOH时，了解如何级联数据是一个巨大的挑战。我的方法是将数据分成3个表，家庭，人和关系。 hh只是存储一个地址和一个id。关系存储person_id_a，person_id_b和关系代码（即，1 =兄弟）。和人存储的名称，和一个hh_id。这样，如果一个家庭分裂，我可以改变妈妈和爸爸之间的关系为NULL，并为爸爸和任何孩子与他一起创造一个新的hh，而不破坏其他关系。[这里是什么模型看起来像：

人
-person_id
-hh_id

关系
-person_id_a
-person_id_b
-relationship_cd

家庭
-hh_id
-address

这对你们有意义吗？你能想到任何原因，这不会工作，或者认为有更好的模型吗？

对不起，我知道这是有点令人沮丧。所有这些离婚使我的工作困难>：（

解决方案

对于名字，我建议保持简单。

FamilyNames

NameType { legal，alternate}

您甚至可以使用FullName的单一名称列，而不是中间名， ，少女姓氏，父亲姓氏，昵称或任何其他特殊名称列，只会使任何名称搜索算法复杂化，并混淆数据输入。以下是一些要考虑的示例名称：

• 约翰·保罗·史密斯


• 玛丽·保罗·史密斯


• 约翰·亨利·威廉·阿特米斯·威廉姆斯


• 玛丽亚·德洛杉矶Gomez Portillo


• Abdul Rahmin ibn Saeed ibn Abd al-Aziz al-Filasteeni


• Abdulla

这些名字显示什么？第一个是很正常的英语名称，第一个（John），中间（Paul） 。第二是玛丽。她的法定名字中没有中间名。她的娘娘姓是保罗，她已婚的姓是史密斯。她使用两者，没有连字符。第三是约翰。他的父母认为给他三个中间名是非常棒的。第四个是西班牙裔的名字，这是她的完整法定名字。她的名字是玛丽亚·德洛杉矶。 de los Angeles是她的拳头名字的一部分。她没有中间名。她父亲的第一个姓是戈麦斯。她母亲的第一个姓是波蒂略。所以玛丽亚的全名是戈麦斯波蒂略。她可能通常只是玛丽亚·戈麦斯，因为她太累了，她的名字进入一个破烂的方式。技术上，波蒂略是她的最后的名字，但如果她只使用她的两个姓氏之一，她会使用第一个（父亲的姓氏）。第五是巴勒斯坦阿卜杜勒阿齐兹孙子萨德的儿子阿卜杜勒·拉明。有乐趣把这放在第一/中/最后。阿卜杜勒·拉明是他的名字。所有其余的是家庭名。最后一个是阿卜杜拉。他来自阿富汗。人们常常没有姓氏。他只是阿布杜拉。他也不知道他的出生日期，因为他来自哪里，他们不在乎（这在美国殖民时期经常是这样的 - 关心出生日期在许多文化中是最近的事情）。

您应该有一个单独的名称表，所以一个人可以有多个名称行。一个例子说明了众多原因之一。玛丽·史密斯讨厌她的通用名。所以她把它合法改变为宇宙的阳光棒棒糖伯爵夫人。她对两年后的遗憾。现在，她有时使用玛丽·史密斯，有时使用阳光宇宙，取决于月亮的阶段。

但是，你这样做，人们会以各种可以想象的方式（和一些你不能想象的）来翻译名字，在不同时间以不同的方式输入它们。如果你不接受作为给定，你会有问题。

对于家庭，正如我在评论中提到的，您可能希望支持多对多关系。一个家庭可以有0到许多人，一个人可以有0到许多家庭。这是一个有点棘手，虽然，因为它取决于你定义为一个家庭。你可能觉得需要把一个人称为主要的，但考虑一个孩子花一半的时间与她的母亲，一半与她的父亲。哪一个是主要的？

Here's my situation. I am building a database to track relationships between people and households. Typically, everything is tied to a "head of household".

I am trying to avoid this as it creates problems when people move (i.e. brother finally gets a job and moves out) or families break up (i.e. dad and mom get divorced, household is now split into two, some kids stay with mom, some with dad).

It is a huge challenge to figure out how to cascade the data when the model is based on HOH. My approach is split the data into 3 tables, household, person, and relationship. hh just stores an address and an id. relationship stores a person_id_a, person_id_b and a relationship code (i.e. 1 = sibling). And person stores the names, and a hh_id. This way, if a family splits up, I can change the relationship between mom and dad to NULL and create a new hh for dad and any kids that came with him without disrupting the other relationships.![Here is what the model looks like:

Person -person_id -hh_id

Relationship -person_id_a -person_id_b -relationship_cd

Household -hh_id -address

Does this makes sense to you guys? Can you think of any reason this wouldn't work, or think there is a better model out there?

Sorry, I know this is a bit depressing. All these divorces making my job difficult >:(

解决方案

For names, I recommend keeping it simple. Have a name table with three columns:

• GivenNames
• FamilyNames
• NameType {legal, alternate}

You can even just go with a single name column for FullName instead. I would not have middle name, married name, maiden name, paternal surname, nickname, or any other "special" name columns. It will only complicate any name searching algorithm, and confuse data entry. Here are some example names to consider:

• John Paul Smith
• Mary Paul Smith
• John Henry William Artemis Williams
• Maria de los Angeles Gomez Portillo
• Abdul Rahmin ibn Saeed ibn Abd al-Aziz al-Filasteeni
• Abdulla

So what do these names show? The first is pretty normal english name, with first (John), middle (Paul) and last (Smith). The second is Mary. She has no middle name in her legal name. Her maiden surname is Paul, and her married surname is Smith. She uses both, without a hyphen. The third is John. His parents thought it would be awesome to give him three middle names. The fourth is a Hispanic name, and that's her full legal name. Her given name is Maria de los Angeles. "de los Angeles" is part of her fist name. She does not have a middle name. Her father's first surname was Gomez. Her mother's first surname was Portillo. So Maria's full surname is Gomez Portillo. She may commonly go by just Maria Gomez because she's so tired of her name getting entered in a mangled way. Technically, Portillo is her "last" name, but if she were to use only one of her two surnames, she would use the first one (the paternal surname). The fifth is Abdul Rahmin, son of Saeed, grandson of Abd al-Aziz, of Palestine. Have fun putting this in first/middle/last. Abdul Rahmin is his given name. All the rest are family names. The final one is Abdulla. He's from Afghanistan. It's not uncommon for folks to have no last name there. He is simply Abdulla. He also doesn't know his birthdate, because where he was from, they don't care about that (this was often the case in American colonial times...caring about birthdates is kind of a recent thing in many cultures).

You should have a separate name table, so a person can have more than one name row. An example illustrates one of the many, many reasons why. Mary Smith hates her common name. So she gets it legally changed to Sunshine Lollipop Countess of the Universe. She sort of regrets that two years later. Now, she sometimes uses Mary Smith, and sometimes Sunshine Universe, depending on the phase of the moon. Store both of them and you find her either way.

However you do it, people will mangle names in every way imaginable (and some you wouldn't imagine), and enter them differently at different times. If you don't accept that as a given, you'll have problems.

For households, as I mentioned in my comments, you may want to support a many-to-many relationship. A household can have zero to many people, and a person can have zero to many households. This is a little tricky though, because it depends on what your define as a household. You may feel the need to denote one as primary, but consider a child that spends half her time with her mother, and half with her father. Which one is primary?

这篇关于数据库模型代表家庭，家庭和关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！