Re: bidirectional mapping?

[Rob Sargent <robjsargent@xxxxxxxxx>]

On 08/02/2017 01:35 PM, John McKown wrote:

On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers <chris.travers@xxxxxxxxx> wrote:

On Wed, Aug 2, 2017 at 5:44 PM, John McKown <john.archie.mckown@xxxxxxxxx> 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).