Re: Advice on a table structure
On Apr 27, 2020, at 19:27, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
The marital status column is redundant, given the other table(s).
Not the way I would do it, because a change in status would require you to update two rows.
Hi,I have one table that stores data about persons with fields like:---------------------------PERSONS TABLE---------------------------* Id* Last name* First name* Gender* Marital statusand 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 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.
I am writing to ask for advice about an optimal approach to model this situation.Respectfully,Jorge Maldonado
В списке pgsql-novice по дате отправления: