Postgresql - create table with disjoint subclasses

Question

I'm unsure how one can create a table on postgresql with disjoint subclasses. I have represented (a very simplified version of) my problem below in an ER diagram, showing the two subclasses and the attributes of each subclass.

For the columns common to all rows (id, common1, common2), it's clearly very simple (as shown in the code below).

create table Music (
    id          serial,
    common1 int not null, 
    common2 boolean not null, 

--<what to put here???>

);

However I'm not sure of the best way of considering the subclass issue. Does anyone know where to go from here?

What do layers, at1, lead, and strings and their values represent? — Schwern
– Schwern, Commented Jul 3, 2020 at 2:56
@Schwern Columns of symphony are id, common1, common2, layers, and at1. Columns of concerto are id, common1, common2, lead and strings. I'm wondering if this can all be considered in a single table named Music. — Mathew Carroll
– Mathew Carroll, Commented Jul 3, 2020 at 3:03
@CarlosBazilio looked through this, but only contains references to books which I don't want to purchase — Mathew Carroll
– Mathew Carroll, Commented Jul 3, 2020 at 3:04

Schwern · Accepted Answer · 2020-07-03 03:20:24Z

Postgres table inheritance would work like so:

create table music (
    id serial primary key,
    common1 int not null, 
    common2 boolean not null
);

create table symphony (
    layers int not null,
    at1 text not null
) inherits(music);

create table concerto (
    lead text not null,
    strings integer not null
) inherits(music);

Consider if we have a row in each table.

insert into concerto (common1, common2, lead, strings)
  values (1, true, 'a', 5);
insert into symphony (common1, common2, layers, at1)
  values (2, false, 3, 'b');
insert into music (common1, common2)
  values (3, true);

They are all rows of music.

-- Fetches id, common1, and common2 from all rows.
select *
from music

If you only want to query rows in music specify only music.

-- Fetches id, common1, and common2 from only the one row in music.
select *
from only music

If you want to use symphony columns, you have to query symphony.

-- Fetches id, common1, common2, layers, at1 only from symphony
select *
from symphony

Try it

A more traditional structure would use join tables like so:

create table music (
    id serial primary key,
    common1 int not null, 
    common2 boolean not null
);

create table music_symphony (
    music_id integer references music(id),
    layers int not null,
    at1 text not null
);

create table music_concerto (
    music_id integer references music(id),
    lead text not null,
    strings integer not null
);

insert into music (id, common1, common2)
  values (1, 1, true);
insert into music_concerto(lead, strings)
  values ('a', 5);

insert into music (id, common1, common2)
  values (2, 2, false);
insert into music_symphony (music_id, layers, at1)
  values (2, 3, 'b');
  
insert into music (id, common1, common2)
  values (3, 3, true);

-- Fetch all symphonies
select *
from music m
join music_symphony ms on ms.music_id = m.id

Try it

Collectives™ on Stack Overflow

Postgresql - create table with disjoint subclasses

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related