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!

I don't think you can rely on a database to save your marriage ;D — glenatron, Dec 07 '10 at 15:53
2 tables. A one to one relationship with an association table. — , Dec 07 '10 at 15:57
"Glenatron ladies and gentlemen! He'll be here all weekend, don't forget to tip your waiters and waitresses!" — Abe Miessler, Dec 07 '10 at 15:57
Cue Henny Youngman...http://www.youtube.com/watch?v=0Nz5TWRRv8A — D'Arcy Rittich, Dec 07 '10 at 16:11
Not really an answer, but you might find this interesting reading: http://explainextended.com/2009/03/07/selecting-friends/ marriage is symmetric and irreflexive (try working that into your next domestic) — araqnid, Dec 07 '10 at 16:14

marcgg · Accepted Answer · 2010-12-07T16:01:15.427

5

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)

edited Dec 07 '10 at 16:01

answered Dec 07 '10 at 15:54

marcgg

65,020
52
178
231

The field maried_to_id should be completed in both persons? It's repeated information, but I can't left it blank in one of them because it wouldn't be true – Mauro Zadunaisky Dec 07 '10 at 15:58
@mauro: yes, it should be filled for both. of course you'll need some logic to make sure that this condition is verified (ie do not delete someone if the person is maried, do not mary to unexisting persons...) – marcgg Dec 07 '10 at 15:59
1

You forgot EndDate !-) And, beeing a bit conservative, I would make it more precise for the name: HusbandId, SpouseId. This ways you enforce entry 1 way. But it wouldn't work with homosexual marriage. – iDevlop Dec 07 '10 at 16:02
@iDevelop: It's funny because it's true 50% of the time. – jason Dec 07 '10 at 16:04
Very helpful, my only addition is that that for the above example to be a true 1:1 relationship you would need to have a UNIQUE constraint on the FK field. – hfrog713 Mar 06 '19 at 11:51
You can sort the ids, always have the smaller id as the first person. That way your data isn't copied and you are pansexually enabled! Dont forget to tweak your joins though! – polvoazul Apr 02 '21 at 19:36

score 2 · Answer 2 · answered Dec 07 '10 at 15:56

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

answered Dec 07 '10 at 15:56

Sualeh Fatehi

4,700
2
24
28

nice - except the use of the verb annullment is arbitrarily restrictive... i would go with begin_dt and end_dt and perhaps an end_reason attribute somewhere else if needed. – Randy Sep 07 '12 at 13:29
Good point Randy. Think widower vs divorcee – polvoazul Apr 02 '21 at 19:38

score 2 · Answer 3 · answered Dec 07 '10 at 16:02

2

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.

answered Dec 07 '10 at 16:02

D'Arcy Rittich

167,292
40
290
283

also, it's possible you will need to store the detailed history information, but also need a performant way of getting any arbtirary person's current spouse, which may need some extra infrastructure... fun :) – araqnid Dec 07 '10 at 16:04

score 0 · Answer 4 · answered Dec 07 '10 at 15:55

0

I would recommend Following structure Lets say table name is Person.

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

.....

No need to create foreign key relation ship.

answered Dec 07 '10 at 15:55

Lav

1,850
15
17

1

Foreign keys should pretty much **always** be enforced using a FK relationship – Jamiec Dec 07 '10 at 16:02
Note that the state is duplicated, as a.MarriedTo(b) should also mean b.MarriedTo(a). – Piskvor left the building Dec 07 '10 at 16:04
yes - the complication of married_to having to be equal in multiple rows is a normalization problem... – Randy Sep 07 '12 at 13:30

ChrisF · Answer 5 · 2010-12-07T16:22:54.420

0

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:

MarriageId
Person1Id
Person2Id
...

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

edited Dec 07 '10 at 16:22

answered Dec 07 '10 at 15:55

ChrisF

134,786
31
255
325

you should add something to indicate how to deal with/prevent having two marriage rows, with the person ids interchanged. – araqnid Dec 07 '10 at 16:14
Can someone please explain how you could implement the point that araqnid is bringing up? Is the only way some trigger on an insert that would look at whats being stored, then look at the inverse and compare the two? – hfrog713 Mar 06 '19 at 11:51
"you should make the combination of MarriageId, Person1Id and Person2Id unique to avoid adding a row where the people are swapped over." – ChrisF Mar 06 '19 at 12:43

score 0 · Answer 6 · answered Dec 07 '10 at 15:58

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.

how to save marital relationship in a database

6 Answers6

Linked