Is there pattern to have union table for different items?

Question

I'd like to have column constraint based combination of 2 columns. I don't find the way to use foreign key here, because it should be conditional FK, then. Hope this basic SQL shows the problem:

CREATE TABLE performer_type ( id serial primary key, type varchar ); INSERT INTO performer_type ( id, type ) VALUES (1, 'singer'), ( 2, 'band'); CREATE TABLE singer ( id serial primary key, name varchar ); INSERT INTO singer ( id, name ) VALUES (1, 'Robert'); CREATE TABLE band ( id serial primary key, name varchar ); INSERT INTO band ( id, name ) VALUES (1, 'Animates'), ( 2, 'Zed Leppelin'); CREATE TABLE gig ( id serial primary key, performer_type_id int default null, /* FK, no problem */ performer_id int default null /* want FK based on previous FK, no good solution so far */ ); INSERT INTO gig ( performer_type_id, performer_id ) VALUES ( 1,1 ), (2,1), (2,2), (1,2), (2,3);

Now, the last INSERT works, but for last 2 value pairs I'd like it fail, because there is no singer ID 2 nor band ID 3. How to set such constraint?

I already asked similar question in Mysql context and only solution was to use trigger. Problem with trigger was: you can't have dynamic list of types and table set. I'd like to add types (and related tables) on the fly.

I also found very promising pattern, but this is upside down for me, I did not figured out, how to turn it to work in my case.

What I am looking here seems to me so useful pattern, I think there must be some common way for it. Is it?

Edit.

Seems, I choose bad items in my examples, so I try make it clear: different performer tables (singer and band) have NO relation between them. gig-table just has to list tasks for different performers, without setting any relations between them.

Another example would items in stock: I may have item_type-table, which defines hundreds of item-types with related tables (for example, orange and house), and there should be table stock which enlists all appearances of items.

PostgreSQL I use is 9.6

I think your database structure could be re-worked. Why not make a table called Musician, and a table called Performer, then make a junction table between the many-to-many relationship of Performer and Musician which has a primary key of the Musicians ID and the Performer ID, then use that tables Combined Primary key as your foreign key inside table Gig, For example, Performer could have bands Cream and Derek and the Dominoes, and Musician has Eric Clapton, who in turn belonged to both Performers(Bands) — Ryan Wilson
– Ryan Wilson, Commented May 16, 2018 at 19:44
@RyanWilson, maybe my example is not clear enough. performer_type may contain hundreds types, every row has corresponding table. The gig-table is itself basically junction table between types-table and corresponding performer table. Can't see how separate junction table can solve problem? — w.k
– w.k, Commented May 16, 2018 at 19:53
That's what I'm trying to steer you away from, why would you want to make seperate tables for each kind of performer?? You could just have a Performer type column in table Performer which says whether it's a singer, band, etc... — Ryan Wilson
– Ryan Wilson, Commented May 16, 2018 at 19:55
This is simplified example, of course. band and singer (and maybe painter and actor etc) have pretty different set of properties on their tables. Those tables may be also different product classes and there would be stock or sales junction tables to have them together in some common aspect, but in basics they are so different objects you can't but them in same table. — w.k
– w.k, Commented May 16, 2018 at 20:05
Ok. You could still do things with the different tables if you need to, but you could change it to use some of what I suggested. A gig is composed of one or many performers, it doesn't care about the performer type and the performer type properties, so you could still have Performer table and Musician table with junction table as I suggested to house both their keys as primary key and use those in Gig, but that Performer table would house a foreign key to Performer-Type which would then link to your relevant performer type tables. — Ryan Wilson
– Ryan Wilson, Commented May 16, 2018 at 20:10

w.k · Accepted Answer · 2018-05-17 09:58:35Z

Based on @Laurenz Albe answer I form a solution for example above. Main difference: there is parent table performer, which PK is FK/PK for specific performer-tables and is referenced also from gig table.

CREATE TABLE performer_type ( id serial primary key, type varchar ); INSERT INTO performer_type ( id, type ) VALUES (1, 'singer' ), ( 2, 'band' ); CREATE TABLE performer ( id serial primary key, performer_type_id int REFERENCES performer_type(id) ); CREATE TABLE singer ( id int primary key REFERENCES performer(id), name varchar ); INSERT INTO performer ( performer_type_id ) VALUES (1); -- get PK 1 for next statement INSERT INTO singer ( id, name ) VALUES (1, 'Robert'); CREATE TABLE band ( id int primary key REFERENCES performer(id), name varchar ); INSERT INTO performer ( performer_type_id ) VALUES (2); -- get PK 2 for next statement INSERT INTO singer ( id, name ) VALUES (2, 'Animates'); INSERT INTO performer ( performer_type_id ) VALUES (2); -- get PK 3 for next statement INSERT INTO singer ( id, name ) VALUES (3, 'Zed Leppelin'); CREATE TABLE gig ( id serial primary key, performer_id int REFERENCES performer(id) ); INSERT INTO gig ( performer_id ) VALUES (1), (2), (3), (4);

And the last INSERT fails, as expected:

ERROR: insert or update on table "gig" violates foreign key constraint "gig_performer_id_fkey" DETAIL: Key (performer_id)=(4) is not present in table "performer".

But

For me there is annoying problem: I have no good way to make distinction which ID is for singer and which for band etc. (in original example I had performer_type_id in gig-table for that), because any performer_id may belong any performer. So I'd like any performer type has it's own ID range, so I create dummy table for every sequence

CREATE TABLE band_id ( id int primary key, dummy boolean default null ); CREATE SEQUENCE band_id_seq START 1; ALTER TABLE band_id ALTER COLUMN id SET DEFAULT nextval('band_id_seq'); CREATE TABLE singer_id ( id int primary key, dummy boolean default null ); CREATE SEQUENCE singer_id_seq START 2000000; ALTER TABLE singer_id ALTER COLUMN id SET DEFAULT nextval('singer_id_seq');

Now, to insert new row into specific perfomer table I have to get next ID for it:

INSERT INTO band_id (dummy) VALUES (NULL);

Trying to figure out, is it possible to solve this process on DB level, or has something to done in App-level. It would be nice, if inserting into band table could:

before trigger inserting into band_id to genereate specific ID
before trigger inserting this new ID into performer-table
include this new ID into INSERT into band

Frist 2 points are easy, but the last point is not clear for now.

Collectives™ on Stack Overflow

Is there pattern to have union table for different items?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related