From: | Chris Travers <chris(dot)travers(at)gmail(dot)com> |
---|---|
To: | John McKown <john(dot)archie(dot)mckown(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: bidirectional mapping? |
Date: | 2017-08-02 15:55:42 |
Message-ID: | CAKt_Zfv7QaCzq2gS4E5_0eYn3cVFsnKyiR3pgx4ha7be=pBp_A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Aug 2, 2017 at 5:44 PM, John McKown <john(dot)archie(dot)mckown(at)gmail(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Alexander Farber | 2017-08-02 15:58:45 | Do not INSERT if UPDATE fails |
Previous Message | John McKown | 2017-08-02 15:44:34 | bidirectional mapping? |