bidirectional mapping?

[John McKown <john.archie.mckown@xxxxxxxxx>]

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

;

--

Veni, Vidi, VISA: I came, I saw, I did a little shopping.

Maranatha! <><
John McKown