How to manage a Type-2 setup from parent to child tables
Greetings,
On Postgres 13, I have a parent table, a child table and a grand child table, with sample record as below. These tables are set in Type-2 design. Meaning, to insert a new record, we end date the existing record and add a new one.
Requirement is to increase the amt in the grand child table, to 30%.
How to end date the current records in each table and carry the FK values into the child and grand child tables? Below is the dbfiddle link for the table layouts.
Can someone kindly help?
ASKER
Thanks for the input!
Dates aren't the issue in my case. This is a made up data and so, wasn't particular about adding correct dates.
I'm looking for a solid example on how to create a new record in the parent table and how that key is used in the child table while the child table gets new records as well.
In the sample, I have outlined only 1 record in the parent table, but in my actual data, there are about a million such.
Looking for a function or a SP, as an example, to be precise.
Thank you!
ASKER
The normal approach is here to store the new data in a staging table. And using this table to do the according updates and inserts.
The end dates are set by a single UPDATE using an equi-join on the new data over the primary key from the relational model.
In the SCD2 there is no "carry over" of those dates. Here the dates are also updated by an equi-join into your staging tables.
btw, your samples are under normal circumstances already incorrect, as in sample 2 and 3 the begin and end dates for income_id 1 and income_inc_id 1 must form non-overlapping intervals.
p.s. for more concrete help, you should explain your actual problem in greater detail.