How to keep marital relations in a database

Question

I need to save this information in a database

Person → married to → Person

Where should I save this information? What is the right design pattern if I have to apply here?

Thank!

Answer 1

If you can marry only one person: 1: 1

------------- - Person - ------------- id (key) maried_to_id (foreign key) 

If you can be married to more than one person or want to track previous games, n: n

 ------------- - Person - ------------- person_id (key) ------------- - Mariage - ------------- first_person_id (foreign key) second_person_id (foreign key) start_date end_date 

(also first_person_id + second_person_id + date forms a unique key for mariage. You can leave a date, but then repeated entries will not be tracked)

Answer 2

Here is a hypothetical scheme that you can use. All people are in one table, and each person has a unique identifier. Marriages are in the table of relations with foreign keys.

 PERSONS - ID - INTEGER, PK - FIRSTNAME - VARCHAR(20) - LASTNAME - VARCHAR(20) - SEX - CHAR(1) - ... any other fields MARRIAGES - PERSON1_ID - INTEGER, FK - PERSON2_ID - INTEGER, FK - MARRIAGE_DATE - DATE - ANULLMENT_DATE - DATE - ... any other fields 

Answer 3

This is a great question for developing a circuit diagram. What seems like a simple problem can easily become quite complex:

For example, how to handle:
- mariages more than two people
- various types of marriage (legal, religious, other)
- parallel marriage
- repeated marriages
- divorce
- independent marriage (hey, this happened at Glee!)

The trick, if any, is to carefully think through all the permutations of what you are trying to model. Only after that do you really go ahead and model it.

Answer 4

I would recommend the following structure. Let's say the table name is Person.

• PersonId (int, Key)
• MarriedTo (int, nullable)

.....

No need to create external relationship relationships.

Answer 5

You can do this with the “Spouse” column in the “Person” table, which can be zero (for the case of an unmarried person).

If you are married, this contains the identifier of the other person as well as the foreign key.

The best solution would be a separate “Marriage” table, in which there are at least three columns:

 MarriageId Person1Id Person2Id ... 

The face ID is the foreign key in the Face table, and you must make a unique combination of the MarriageId, Person1Id, and Person2Id characteristics to avoid adding a row in which people swap places.

Although it should be noted that both of these models are quite simple and make assumptions about how many people can be in the same marriage;)

Answer 6

It sounds like using a simple lookup table - the important part consists of two fields: one foreign key for the Person1 ID field, the other a foreign key for the Person2 ID field. Any details about the marriage (dates still current, etc.) will also be stored in this table.

This would make it easier for people with multiple marriages, polygamous relationships, and so on. If you need a simple 1: 1 relationship, you can simply include the foreign key link to your spouse in the user’s field, but it will be much less flexible.