On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers <chris.trav...@gmail.com> wrote:
> > > 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); > I _knew_ there must be a better way. I just didn't see it. Many thanks! > > 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: > I need to become familiar with "gin" indices, I guess. I'm a bit behind in my knowledge of PostgreSQL. I also try to use "plain old SQL" as defined in the "standard". Mainly because I use both PostgreSQL and SQLite. > > 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 > >> >> > -- > Best Wishes, > Chris Travers > > Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor > lock-in. > http://www.efficito.com/learn_more > -- Veni, Vidi, VISA: I came, I saw, I did a little shopping. Maranatha! <>< John McKown
