bidirectional mapping?

From: John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com>
To: PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: bidirectional mapping?
Date: 2017-08-02 15:44:34
Message-ID: CAAJSdjgjMbeQ=9DG3_LxQvDLbarS1b6q22-YfQ74d4KLAWPN-w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris Travers 2017-08-02 15:55:42 Re: bidirectional mapping?
Previous Message Melvin Davidson 2017-08-02 14:13:32 Re: org.postgresql.util.PSQLException: FATAL: terminating connection due to administrator command