0
My code automatically inserts data into a database, making the BD too large and sometimes with repeated information.
For this, I want to use the Slowly Changing Dimension (SCD) method so that:
- A record with a certain id will be added to the database, and the insertion date will be stored in the field data_inicial
- As long as this record is equal, the program will only update the
data_final - When something changes in this record,
data_finalthe record will be updated to the date the change took place and another record will be created with the information that has changed and thedata_inicialupdated. As represented below:
id | city | start date | end date
123 | São Paulo | 01/10/2016 | 03/12/2016
123 | Rio de Janeiro | 04/12/2016 | 20/01/2017
123 | Fortaleza | 21/01/2017 | 01/01/2999
Obs.: the table will have data from multiple id’s, and more than one die with the same id.
Obs2.: I use a C# code to insert into the database
Adding integrity constraints to the database does not resolve for you to avoid replications?
– Giuliana Bezerra
My idea was to implement the Slowly Changing Dimension Type 6, which would leave my table the way I presented it in the question. But since the code automatically inserts it into the table, and the final data_is unpredictable, I’m not able to solve it. I even used Unique Constraint with initial data_and id, but it does not solve the same data replication problem in its entirety.
– João Gabriel F. Marins