Postgres Pro
Downloads
Home   >   mailing lists

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

From Mark Wallace
Subject Re: Advice on a table structure
Date
Msg-id CEF352ED-708B-4263-B6D8-870CA5165260@acm.org
Whole thread Raw
In response to Advice on a table structure  (JORGE MALDONADO <jorgemal1960@gmail.com>)
List pgsql-novice
Tree view
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

pgsql-novice by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: Advice on a table structure
Next
From: David Roper
Date:
Subject: Re: Advice on a table structure