On Wed, Aug 2, 2017 at 5:44 PM, John McKown <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); This way you can ensure that each relationship is only recorded once. Then I would create a function that returns an array of the parties. CREATE OR REPLACE FUNCTION parties(marriage) returns int[] language sql as $$ select array[$1.party, $1.counterparty]; $$; Then you can create a gin index: create index marriage_parties_idx on marriage using gin(parties(marriage)); Then you can query on: select ... from people p1 where first_name = 'Ashley' join marriage m on p1 = any(marriage.parties) join people p2 on p2 = any(marriage.parties) and p2.id <> p1.id > > > -- > Veni, Vidi, VISA: I came, I saw, I did a little shopping. > > Maranatha! <>< > John McKown > -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
