3

We are updating table XYZ have following fields: 

First Name|Middle Name|Last Name|Address|DOB|Country|County|(etc.)

Initially, we are calling some web service which is sending updated information for a row in XYZ like either update first name or DOB update or both or all or none.

Now there is requirement to create a log table in database which store summary of old records and changes done to XYZ. Every affected row should be reported.

Is it good to create similar fields in new table say ABC:

First Name|Middle Name|Last Name|Address|DOB|Country|County|Update_Date

with additional field called "Update_datetime"

Now each time service called we will select values from previous row i.e from XYZ and update the same to ABC with update date.

What are loopholes in this practice? What other better practices can be followed?

Cœur
  • 37,241
  • 25
  • 195
  • 267
fatherazrael
  • 5,511
  • 16
  • 71
  • 155

2 Answers2

1

Is there a requirement for a log table or a requirement for a proper history?

Oracle has history functionality Out of the box

I doubt MySQL does - you may have to do a different way.

The pros of Oracle is that it will not fail - it's a core feature. The cons of hand rolled is, well, it's hand rolled. Lots of SPs, triggers or other nastiness that people can deliberately or inadvertently bypass.

LoztInSpace
  • 5,584
  • 1
  • 15
  • 27
1

I echo the need to know what the requirements are behind this. Is it to be human readable (auditing, debugging etc.) or machine readable (e.g. event sourcing architectural pattern)? How often will you need to go back to look at previous versions? How often do things change?

If it's event sourcing, then there are a few answers around on Stack Overflow about that, e.g. Using an RDBMS as event sourcing storage and best event sourcing db strategy. For more of an introduction, there's e.g. a Martin Fowler video.

There are also SO answers on logging changes in MySQL and Using MySQL triggers to log all table changes to a secondary table and an alternative approach (using 1 table, but adding sort-of version numbers to show each record's validity).

Community
  • 1
  • 1
Bob Salmon
  • 411
  • 4
  • 10