We have a requirement to design a relational database database. Requirement is given below
- Tables should allow only insert.
- Every table will have "LastUpdatedDate" and "LastUpdatedAuthor" information.
- If the table requires delete operation on records, then table will have "EffectiveStartDate" and "EffectiveEndDate".
- Every table will have Effective Start Date column.
- Every Update will be treated as an insert with latest timestamp in LastupdatedDate field with Author name.
- Every Delete will update the rocod's EffectiveEndDate to previous date and considered as inactive records.
- Tables should not suppert hard delete and soft delete.
Based on this requirement, my colleague has designed a table as below.
USER_IDINT PRIMARY KEY
STREETVARCHAR(50) NOT NULL,
ZIPCODEVARCHAR(50) NULL DEFAULT NULL,
LAST_CHANGED_USERVARCHAR(50) NOT NULL,
LAST_CHANGED_DTTIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
PREVIOUS_RECORD_IDVARCHAR(50) NULL DEFAULT NULL,
EFFECTIVE_START_DTDATE NOT NULL,
EFFECTIVE_END_DTDATE NOT NULL,
Here, if we need to update a record, we will create a new record and old records Effective end date will be updated with past date and a new record will be created with changed data. Also, PREVIOUS_RECORD_ID of the old record will be updated with new records ID and PREVIOUS_RECORD_ID of the new record will be "Null".
Do you think this is a good approach, especially updating PREVIOUS_RECORD_ID ? what are the problems with this approach. What is the best design pattern for this requirement.
Aucun commentaire:
Enregistrer un commentaire