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:
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) )
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!