Database Design - Transaction Strategy

• craigbroadman

  SSC Veteran

  Points: 233

  More actions

  January 13, 2011 at 3:36 am

  #232197

  Hopefully I can explain this correctly....

  We are currently contemplating applying some logic adopted from our legacy application written in Pascal using B-Tree filer system for it's data store. We are developing in .NET using SQL Server.

  Basically, the way this is implemented is to write a transaction (to a generic file) that then "triggers" an action on the live file corresponding to the record type and transaction type. To explain further I will try to give an example:

  Let's say we have a Customer File/Table....

  I want to update a Customer, to do this it must follow this logic:

  1. Insert record in to transaction file/table (Record Type = "Customer", Transaction Type = "Update")

  2. This then calculates that the change must be made to the live Customer file/table.

  I want to then insert a Customer, to do this it must follow this logic:

  3. Insert record in to transaction file/table (Record Type = "Customer", Transaction Type = "Insert")

  4. This then calculates that the insert must be made to the live Customer file/table.

  Imagine this same logic applied to every file/table (could be hundreds).

  The advantage that this gives in the Pascal/B-Tree system is that if the Customer file gets blown away it can easily be reconstructed from the Transaction records (we call this "Replayability". It also gives a full history of what has happened on the database.

  At this stage I am not quite sure how this would be implemented, but what I am really looking for is if someone else has used this same strategy in their systems?

  If not, does anyone have any thoughts on if this is actually needed in a system using SQL Server?

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  January 13, 2011 at 8:32 am

  #1273086

  User has also posted this question to:

  http://www.sqlservercentral.com/Forums/Topic1047273-373-1.aspx

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]