Re: Advice on a table structure

On Monday, April 27, 2020, JORGE MALDONADO <jorgemal1960(at)gmail(dot)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.