On Monday, April 27, 2020, JORGE MALDONADO <
jorgemal1960@gmail.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.
