1

I'm working on a project that is a sort of bill of materials manager that needs to support revisioning of components. After some research, I've stumbled upon slowly changing dimensions type 2 and type 4 which seems like the most appropriate pattern in this use cases.

However, I'm a little bit confused on a couple of concepts:

CREATE TABLE projects (
  id    INTEGER PRIMARY KEY,
  name  TEXT
)

CREATE TABLE components (
  id           INTEGER PRIMARY KEY,
  name         TEXT,
  project_id   INTEGER,
  CONSTRAINT fk_project FOREIGN KEY (project_id) REFERENCES projects (id)
)

If I wanted to implement SCD Type 2, would the components table have the additional columns for start_date, end_date, active? Or, would Type 2 be adding a revisions table with the same structure as components with the additional columns and Type 4 having a revisions table and a revisions_history table?

Any help would be greatly appreciated!

user1960118
  • 367
  • 6
  • 17
  • 1
    Your tables seem to be relevant to an OLTP system and SCD is a mechanism used in Kimball-style OLAP systems - so would not be relevant to what you are doing. Are you trying to track different components associated to a project over time or are you trying to track changes to individual project or component records? – NickW Sep 04 '21 at 18:29
  • @NickW It would be both, the components can be a hierarchy (so just an additional parent_id on the components table), but I would need to track individual records (the data like component name, qunaity, etc) and how they changed, as well as if a component was added/deleted in a project. So basically a snapshot of what the state of the project looked like at a specific point in time. And then being able to say "at s specific point in time, `component_1` looked like this, and having the ability to rollback to that version – user1960118 Sep 04 '21 at 18:59

1 Answers1

0

You can use temporal_tables

System-period data versioning (also known as transaction time or system time) allows you to specify that old rows are archived into another table (that is called the history table).

https://pgxn.org/dist/temporal_tables/

Amira Bedhiafi
  • 8,088
  • 6
  • 24
  • 60