Re: bidirectional mapping?

[John McKown <john.archie.mckown@xxxxxxxxx>]

On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers <chris.travers@xxxxxxxxx> wrote:

On Wed, Aug 2, 2017 at 5:44 PM, John McKown <john.archie.mckown@xxxxxxxxx> 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