On 08/02/2017 01:35 PM, John McKown wrote:
On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers
<chris.trav...@gmail.com <mailto:chris.trav...@gmail.com>>wrote:
On Wed, Aug 2, 2017 at 5:44 PM, John McKown
<john.archie.mck...@gmail.com
<mailto:john.archie.mck...@gmail.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).
