Advice on a table structure

From: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: Advice on a table structure
Date: 2020-04-27 23:27:28
Message-ID: CAAY=A7-iQkriVmELMrAr7L3ebhkWBgBGqcZyKmxegFxJ2gD0SQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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>
<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Gavin Flower 2020-04-28 00:46:30 Re: Advice on a table structure
Previous Message Steve Tucknott (TuSol) 2020-04-26 08:02:27 Re: LATIN9 - hex in varchar after convert