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
