Re: Advice on a table structure

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