Postgres Pro
Downloads
Home   >   mailing lists

Thread: Advice on a table structure

Advice on a table structure

From
JORGE MALDONADO
Date:
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

Re: Advice on a table structure

From
Gavin Flower
Date:
On 28/04/2020 11:27, JORGE MALDONADO wrote:
> 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
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> 
>     Libre de virus. www.avast.com 
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> 
>
>
Note that in some countries people of the same sex can legally be 
married, and I think Muslim men are allowed 4 wives.

How will you represent a marriage that existed in the past, but the two 
people have married other partners?

Also not everyone is definitely of one gender.  If you do research on 
this, you will find it is a whole l.ot more complicated than most people 
realize!


Cheers,
Gavin

Re: Advice on a table structure

From
"David G. Johnston"
Date:
On Monday, April 27, 2020, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

---------------------------  
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.


What criteria are you trying to optimize?

Regardless, in SQL its generally safe to add a table when you want to model a relationship between two entities.  You mainly trade flexibility for ease of use.  In this case writing a custom set of triggers to ensure whatever constraints you want are kept during data change.  Or at least that the entry for the husband column must be a male person which you can then combine with a unique index.  And change those rules when you realize the world isn’t so binary.

David J.

Re: Advice on a table structure

From
Mark Wallace
Date:
On Apr 27, 2020, at 19:27, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:

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.

The marital status column is redundant, given the other table(s).

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 would think about adding a FAMILY EVENTS table:

- Id
- Event (for example, any one of: engagement, marriage, separation, divorce, death, widow, widower)
- Event date (when the event occurred)
- (You could add attributes for location, officiant, etc.)

And then replace the COUPLES table by a PERSONS EVENTS table:

- id
- Person (represents an Id of PERSONS table)
- Person role (for example: husband or wife or spouse or partner, etc.)
- Event (represents an Id of FAMILY EVENTS table)

Depending on how you set up the constraints, you can require heterosexual marriage or not, and allow polygamy or not. But if the specifications on same sex marriage or polygamous marriage change, the database design is not impacted.

Finally, a person’s status is represented by their most recent event in the FAMILY EVENTS table (reached by joining through the PERSONS EVENTS table).

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.

Not the way I would do it, because a change in status would require you to update two rows.

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

Respectfully,
Jorge Maldonado



Libre de virus. www.avast.com

Re: Advice on a table structure

From
David Roper
Date:
Hi Jorge,

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

Sent from my iPhone

On 28 Apr 2020, at 00:27, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:


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