how to save marital relationship in a database

Question

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!

Answer 1

If you can only be maried to one person: 1:1

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

If you can be maried to more than one person or want to keep track of previous mariages, 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 form a unique key for mariage. You could leave out the date, but then remariages wouldnt be tracked)

Answer 2

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.

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