Обсуждение: [GENERAL] bidirectional mapping?
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
;
Veni, Vidi, VISA: I came, I saw, I did a little shopping.
Maranatha! <><
John McKown
John McKown
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 UNIQUECHECK( 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 ASSELECT PERSON, SPOUSE FROM forespouseUNIONSELECT 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)
--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.
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 UNIQUECHECK( 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 ASSELECT PERSON, SPOUSE FROM forespouseUNIONSELECT 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)--Best Wishes,Chris TraversEfficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
Veni, Vidi, VISA: I came, I saw, I did a little shopping.
Maranatha! <><
John McKown
John McKown
On 08/02/2017 01:35 PM, John McKown wrote:
Not sure I agree with the uniqueness of the parties involved. Unique on (party, counterparty) isn't a for sure, if there's any temporal dimension involved, in which case I would prefer (id, party, counterparty).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 UNIQUECHECK( 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 ASSELECT PERSON, SPOUSE FROM forespouseUNIONSELECT 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);