mardi 26 février 2019

Database design that support only softdelete and softupdate

We have a requirement to design a relational database database. Requirement is given below

  1. Tables should allow only insert.
  2. Every table will have "LastUpdatedDate" and "LastUpdatedAuthor" information.
  3. If the table requires delete operation on records, then table will have "EffectiveStartDate" and "EffectiveEndDate".
  4. Every table will have Effective Start Date column.
  5. Every Update will be treated as an insert with latest timestamp in LastupdatedDate field with Author name.
  6. Every Delete will update the rocod's EffectiveEndDate to previous date and considered as inactive records.
    1. 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