On 08/02/2017 01:35 PM, John McKown wrote:
> On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers
> <chris(dot)travers(at)gmail(dot)com <mailto:chris(dot)travers(at)gmail(dot)com>>wrote:
>
>
>
> On Wed, Aug 2, 2017 at 5:44 PM, John McKown
> <john(dot)archie(dot)mckown(at)gmail(dot)com
> <mailto:john(dot)archie(dot)mckown(at)gmail(dot)com>> wrote:
>
> Is there a simple way to do bidirectional mapping of a table
> with itself? I am thinking of a "spousal" type relationship,
> where it is true that if A is spouse of B, then B is spouse of
> A. I don't necessarily want "A" and "B" to be a monogamous
> relationship because that is not always be true world wide.
> The best I can come up with so far is something like:
>
> CREATE TABLE forespouse (PERSON integer PRIMARY KEY,
> SPOUSE integer UNIQUE
> CHECK( PERSON != SPOUSE) -- sorry, can't marry self
> );
> CREATE UNIQUE INDEX ON forespouse(PERSON, SPOUSE);
> CREATE UNIQUE INDEX ON forespouse(SPOUSE, PERSON);
> -- I'm not sure that the above indices are needed.
> CREATE VIEW backspouse AS SELECT SPOUSE, PERSON FROM forespouse;
> CREATE VIEW spouse AS
> SELECT PERSON, SPOUSE FROM forespouse
> UNION
> SELECT SPOUSE, PERSON FROM backspouse
> ;
>
>
> Usually the way I have done this is to normalise the
> representation and use a table method for converting for joins.
> In other words:
>
> create table marriage (party integer primary key, counterparty
> integer unique, check party < counterparty);
>
Not sure I agree with the uniqueness of the parties involved. Unique on
(party, counterparty) isn't a for sure, if there's any temporal
dimension involved, in which case I would prefer (id, party, counterparty).