Sample Header Ad - 728x90

Implementing revisioning logic with SCD2/4

0 votes
1 answer
849 views
I posted this question on StackOverflow, but it didn't get much luck so I'm hoping I could have better luck here. 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!
Asked by user1960118 (143 rep)
Sep 5, 2021, 01:58 PM
Last activity: Apr 15, 2023, 08:01 AM