如何在数据库中保存婚姻关系
我必须将此信息保存在数据库中
Person ->已结婚 ->人员
我应该在哪里保存该信息？我应该在这里应用什么正确的设计模式？
谢谢你！
I have to save this information in a database
Person -> is married to -> Person
Where should I save that information? What is the proper design pattern should I apply here?
Thank you!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
如果您只能与一个人结婚：1:1
如果您可以与多个人结婚或想要跟踪以前的婚姻，则 n:n
（也称为first_person_id + secondary_person_id + date 形成婚姻的唯一键。您可以省略日期，但这样再婚就不会被追踪）
If you can only be maried to one person: 1:1
If you can be maried to more than one person or want to keep track of previous mariages, n:n
(also first_person_id + second_person_id + date form a unique key for mariage. You could leave out the date, but then remariages wouldnt be tracked)
这是您可以使用的假设架构。所有的人都在一张表中，每个人都有一个唯一的id。婚姻位于带有外键的关系表中。
Here is a hypothetical schema you can use. All people are in a single table, and each person has a unique id. Marriages are in a relationship table, with foreign keys.
这是教学模式设计的一个很好的问题。看似简单的问题很容易变得相当复杂：
例如，如何处理：
- 两人以上的婚姻
- 不同类型的婚姻（合法、宗教、其他）
- 同时结婚
- 重复婚姻
- 离婚
- 自婚（嘿，这发生在《欢乐合唱团》中！）
诀窍（如果有的话）就是仔细思考你想要建模的所有排列。只有这样你才能真正继续进行建模。
This is a great question for teaching schema design. What seems like a simple problem can easily become quite complicated:
E.g., how to handle:
- mariages of more than two people
- different types of marriage (legal, religious, other)
- concurrent marriages
- repeat marriages
- divorce
- self-marriage (hey, it happend on Glee!)
The trick, if there is one, is to carefully think out all the permutations of what you are trying to model. Only then do you actually go ahead and model it.
我会推荐以下结构
假设表名称是 Person。
nullable)
.....
无需创建外键关系。
I would recommend Following structure
Lets say table name is Person.
nullable)
.....
No need to create foreign key relation ship.
这听起来像是一个简单查找表的用途 - 重要的部分是有两个字段，一个是 Person1 的 ID 字段的外键，另一个是 Person2 的 ID 字段的外键。有关婚姻的任何详细信息（日期、是否仍然有效等）也将存储在此表中。
这将有利于那些有过多次婚姻、一夫多妻关系等的人。如果您想要一个简单的 1:1 关系，您可以只在人员字段中包含对配偶的外键引用，但这会大大降低灵活性。
This sounds like a use for a simple lookup table- the important part is having two fields, one a foreign key for Person1's ID field the other a foreign key for Person2's ID field. Any details about the marriage ( dates, whether it is still current and so on ) would also be stored in this table.
That would facilitate people having had multiple marriages, polygamous relationships and so on. If you want a simple 1:1 relationship you could just include a foreign key reference to the spouse in the person field, but it would be considerably less flexible.
您可以使用“个人”表上的“配偶”列来完成此操作，该列可以为空（对于未婚者的情况）。
如果已婚，则它保存另一个人的 ID，就像外键一样。
更好的解决方案是一个单独的“Marriage”表，该表至少包含三列：
人员 ID 是“Person”表中的外键，您应该使 MarriageId、Person1Id 和 Person2Id 的组合唯一，以避免在其中添加行人被调换了。
尽管应该指出的是，这两个模型都非常基本，并且对一段婚姻中可以有多少人做出了假设；）
You could do it with a "Spouse" column on the "Person" table which can be null (for the case of an unmarried person).
If married this holds the id of the other person, as is a foreign key.
A better solution would be a separate "Marriage" table that has at least three columns:
The person id's are foreign keys into the "Person" table, and you should make the combination of MarriageId, Person1Id and Person2Id unique to avoid adding a row where the people are swapped over.
Though it should be pointed out that both these models are quite basic and make assumptions about how many people can be in one marriage ;)
有婚姻模式。
您需要一个事件（例如，婚姻、审判、飞行、比赛等）和参与者。
对于事件，事件表至少包含 start_date（可能是事件的主键）并且通常包含 end_date。
如果是婚姻，我们创建一个带有属性的婚姻表：
我们可以添加属性。例如，举行仪式的地方。
参与者来自具有以下属性的人员表：
有一个 event_vs_participant 表将事件和参与者关联起来。
该表有一个 role_code 属性。对于婚姻，您将获得伴侣、司仪、证人等角色。对于审判，您将获得法官、证人、律师等角色。对于航班，您将获得乘客、机组人员等角色。对于比赛你可以扮演竞争者、裁判员、观众等角色。
如果是婚姻，我们创建一个包含以下属性的 wedding_vs_participant 表：
该表的外键包含表 wedding、person 和 role。
您应该避免创建具有以下属性的表：
此类表包含人员列表，并且不符合第一范式。
There is a marriage pattern.
You need an event (e.g., a marriage, a trial, a fligth, a match, etc.) and participants.
For the event there is and event table with at least start_date (could be a primary key for the event) and quite often an end_date.
In case of a marriage, we create a marriage table with attributes:
We could add attributes. E.g., for the place where the ceremony take place.
The participants are from a person table with attributes:
There is a event_vs_participant table that associates event and participants.
That table has a role_code attribute. For a marriage, you get the roles partner, officiant, witness, etc. For a trial, you get the roles judge, witness,lawyer, etc. For a flight, you get the roles passenger, cabin crew, etc. For a match you get the roles contender, referee, spectator, etc.
In case of a marriage, we create a marriage_vs_participant table with attributes:
That table has foreign keys with tables marriage, person and role.
You should avoid the creation of a table with attributes:
Such table contains a list of persons and does not reach the first normal form.