how to save marital relationship in a database

I have to save this information in a database

Person -> is married to -> Person

Wh开发者_如何学JAVAere should I save that information? What is the proper design pattern should I apply here?

Thank you!

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)

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

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.

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

1. PersonId (int, Key)
2. MarriedTo (int, nullable)

.....

No need to create foreign key relation ship.

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.

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