Re: Advice on a table structure - Mailing list pgsql-novice

Under the assumption that you wish only to record the current state of the world (i.e. whether some person is currently married, not whether they have historically ever been married and that you are only interested in marriage, not civil partnership or co-habitation), you could simply replace your “marital status” attribute with a foreign key “spouse” that references another Person.

That would, of course, also model same sex marriage - if you want to preclude that you’ll need a trigger on INSERT to check that the referenced Person is not the same gender. From a modelling perspective spouse could represent a civil partner or co-habitee, you’d need the application to enforce any strict rule that it be solely used for legal marriage. If you want to differentiate these positions, then you’ll have to use an associative table like Couples, to which you can add a ”relationship type” attribute, dates from and to or other details of the actual relationship. 

All the best

David



Hi,

I have one table that stores data about persons with fields like:

---------------------------

PERSONS TABLE

---------------------------  

* Id

* Last name

* First name

* Gender

* Marital status

and other information that relates to a single person.

One person might have a husband if female, or a wife if a male. So I thought about adding a table that associates couples like this:

---------------------------  

COUPLES TABLE

---------------------------  

* Id

* Husband (represents an Id of PERSONS table)

* Wife  ( represents an Id of PERSONS table)

One restriction is that one person cannot have more than one spouse.

I also thought about adding a field to the PERSONS table specifying his/her spouse but it seems to me that this approach represents a kind of "circular" relation between 2 records.

I am writing to ask for advice about an optimal approach to model this situation.

Respectfully,

Jorge Maldonado

Libre de virus. www.avast.com