CODEWITHSUNDEEP
database-designdata-modeling
BuZain
BuZain

Reputation: 168

data model design pattern that holds data changes until authorized by another user

Is there a design pattern to consider when building a data model for a banking application that requires data changes to go through an authorization level?

For example, if admin1 changes the telephone number for customer1, the change should not be effective until admin2 authorizes it.

The solution we plan to implement is to have a temp table to hold the changed record with the changed values and once the authorizer approves the change, then we update the main table. This works fine when you have few tables, but would be cumbersome as tables increase.

Upvotes: 3

Views: 1395

Answers (3)

mevdiven
mevdiven

Reputation: 1902

I designed something like this and here's the gist of it;

  1. I create mirror table for every table that I want to have row level version control. Let's say you have CUSTOMER table. Your mirror version control table will be VER_CUSTOMER Every table that I want to have row level version control has a column called RECORD_ID (GUID)
  2. When a record inserted to that table, I generate new GUID and populate that field. New record also inserted into VER_CUSTOMER table with RECORD_ID as added to table's natural PK. When record is updated, I generate new GUID again. Populate RECORD_ID with this new GUID. Updated record also goes to VER_CUSTOMER table.
  3. When record is deleted, I mark record on CUSTOMER table as DELETED (not physically delete the record). I have IS_DELETED column on every table. I set that column to TRUE when record is attempted to be deleted. Again copy of the deleted record also goes into VER_CUSTOMER table.
  4. So every transaction that you have on that table, you have a corresponding record in VER_CUSTOMER table with RECORD_ID and table's natural PK as PK. For example if CUSTOMER table's PK is CUST_ID. PK of VER_CUSTOMER will be composite CUST_ID and RECORD_ID.

Hope this helps...

Upvotes: 0

Novitzky
Novitzky

Reputation: 4876

This works fine when you have few tables, but would be cumbersome as tables increase

Not necessarily. It will work fine with a large amount of tables if you design it properly. You can build a nice and small data model just to hold the changes. It doesn't require you to create a copy of each table.

e.g. You can have a tables like: AuditTables, AuditColumns, AuditChanges, AuditChangesDetails etc. and you can store all changes you need in that model, rather than create a temp table which corresponds with the "live" table.

Upvotes: 1

J. Ed
J. Ed

Reputation: 6742

I'm not aware of any design pattern, but I think I might have another idea for you-
have just one other table, called 'Pending_Changes' with columns 'Table_Identifier', 'Column_Identifier' 'Record_Identifier' and 'New_Value'.
Each row will represent a single column change to some record at some table.
For example- a row with values of ('Customers', 'Phone_Number', '12345', '077-4453432') would be used to represent a change in the phone number of customer 12345.
The couple of downsides of this method is that-
1. all your tables must have a single ID field
2. a change to a single record can span multiple rows in the PendingChanges table, since it keeps a row for every changed column value.

On the upside- it's quite extensible and fairly easy to maintain.

Upvotes: 1

Related Questions