Re: bidirectional mapping?

On Wed, Aug 2, 2017 at 10:55 AM, Chris Travers <chris(dot)travers(at)gmail(dot)com>
wrote:

>
>
> 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);
>

​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