Postgres Pro
Downloads
Home   >   mailing lists

Re: [GENERAL] bidirectional mapping? - Mailing list pgsql-general

From Chris Travers
Subject Re: [GENERAL] bidirectional mapping?
Date
Msg-id CAKt_Zfv7QaCzq2gS4E5_0eYn3cVFsnKyiR3pgx4ha7be=pBp_A@mail.gmail.com
Whole thread Raw
In response to [GENERAL] bidirectional mapping?  (John McKown <john.archie.mckown@gmail.com>)
Responses Re: [GENERAL] bidirectional mapping?  (John McKown <john.archie.mckown@gmail.com>)
List pgsql-general
Tree view


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

pgsql-general by date:

Previous
From: John McKown
Date:
Subject: [GENERAL] bidirectional mapping?
Next
From: Alexander Farber
Date:
Subject: [GENERAL] Do not INSERT if UPDATE fails