5

I want to set up a database with different schemas that correspond to different data-entry systems. There needs to be a “master” (administration) schema with a “parties” table, and multiple other schemas with their own “parties” tables that inherit fields from the master parties table

Here’s a simplified example of the structure:

'PARENT'

CREATE TABLE master.parties
(
  party_key serial NOT NULL,
  name text,
  date_of_birth date,
  phone text,
  CONSTRAINT "PK_master_parties" PRIMARY KEY (party_key)
)

'CHILD'

CREATE TABLE corporate.parties
(
  corp_key integer,
  corp_role text
) INHERITS (master.parties)

I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

This solution I found on another post (https://stackoverflow.com/questions/29509162/postgres-table-inheritance-move-from-parent-to-child-and-vice-versa) was very close to what I am looking for:

WITH deleted AS (
  DELETE FROM ONLY master.parties 
  WHERE party_key = 1
  returning *
)
INSERT INTO corporate.parties (name, date_of_birth, phone, corp_key, corp_role)
SELECT name, date_of_birth, phone, 2, 'President'
from deleted;

However, it seems to me that there should be a better way of adding a row from the 'parent' table to the 'child' table without having to delete the existing row in the 'parent' table.

This solution also poses another, more serious problem: if a change is made to the parties table in the master schema (eg. new column added), I don’t want to have to update all the data-entry systems corresponding to the sub-schemas to include that new column in every INSERT statement. If I were to miss an INSERT statement in code, or if it wasn't possible to immediately update one of the data-entry systems, I’d risk losing the values in that new column of the master.parties table when I try to bring down a row into another schema’s party table (as it is not included in the existing INSERT statement).

Basically what I’m after is to have the ability to add a row to a 'child' table from the 'parent' table, without necessarily knowing all of the columns in the 'parent' table and without creating a duplicate row in the 'parent' table. Is this possible? Should I be reconsidering my DB design?

Any help or suggestions are much appreciated!

  • You should never INHERITS on a table. – Evan Carroll Jul 5 '18 at 3:18
  • 1
    Isn't that why it's called Table Inheritance? Do you know if there is a way to accomplish what I'm after without inheritance? – user2437443 Jul 5 '18 at 16:33
  • that's a much better question. I can answer that. – Evan Carroll Jul 5 '18 at 16:35
  • Could you provide some sample data? – Evan Carroll Jul 5 '18 at 16:49
0
+50

No one (or rather very few) uses "table inheritance", like that. That's a half-baked PostgreSQL feature which is used for some internal underpinnings exposed to the user in a fashion likely to confuse them. I'd highly suggest avoiding it. For more information see,

In your case though you can use a single-table hierarchy or self-referencing hierarchy,

CREATE SCHEMA master;
CREATE TABLE master.entities
(
  entity_id       int  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  parent_id       int  REFERENCES master.entities,
  entity_name     text,
  entity_data      jsonb
);

INSERT INTO master.entities VALUES
  ( 1, null, 'Master', null),
  ( 2, 1   , 'Corp'  , '{"date_of_birth":"2018-07-05"}');

I would like to be able to bring down a row that was added to the master.parties table and insert it into any of the parties tables in the other schemas (ie. corporate), without creating a duplicate row in the master table.

I'm not 100% sure what you mean here. Essentially the relations are not meta-data, it's data. So you have to have a duplicate row to have one entity under two parents. If you want to minimize duplication of the JSONB though you could outsource that to another table -- though I would probably not bother with it unless you have a desired answer as to how two parties will manage the data-stored in the jsonb.

Querying the above structure is slightly more complex, but you'll get the hang of it, you have to use a recursive CTE. You can find lots of examples of this under ,

WITH RECURSIVE t(id,parents,name, data) AS (
  SELECT entity_id, ARRAY[]::int[], entity_name, entity_data
  FROM master.entities
  WHERE parent_id IS NULL
  UNION ALL
    SELECT e.entity_id, parents||parent_id, e.entity_name, e.entity_data
    FROM t
    INNER JOIN master.entities AS e
      ON e.parent_id = t.id
)
TABLE t;

 id | parents |  name  |              data               
----+---------+--------+---------------------------------
  1 | {}      | Master | 
  2 | {1}     | Corp   | {"date_of_birth": "2018-07-05"}
(2 rows)

Note this schema also permits N-level relationships (you can create an almost infinite amount of children under children -- no additional tables required)

  • The "single-table/self-referencing hierarchy" seems like overkill for what I'm after, but your comments on table inheritance have encouraged me to look for alternative solutions. I think I will re-consider my database design to only have one parties table in the Master schema. Thanks for the comprehensive answer! – user2437443 Jul 9 '18 at 2:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.