HowTo: Update DB row with save for prior to change state

Question

Hi, i have a table with order details. User is able to modify these details after he adds them for first time. My question is what is more right way to save prior to update changes. By that i mean not just subjective way of thinking but drawbacks in each way i list or maybe your suggestion...

I thought of creating Orders history table which will be identical to Orders so once i update an order, old one moves to HistoryOrders and this Order is updated.

Other way was to create new Order each time there is an update while using parent field in Order, ie. first Order has null as parentOrderID while update has prior to update orderID in parentOrderID column row...

I need later to work with that data so i need the most flexible solution... maybe i miss another option...

CREATE TABLE [dbo].[Orders](
    [orderID] [int] IDENTITY(1,1) NOT NULL,
    [userID] [int] NOT NULL,
    [paymentMethodID] [tinyint] NOT NULL,
    [orderAmount] [smallint] NOT NULL,
    [orderStatusID] [tinyint] NOT NULL,
    [date] [smalldatetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [orderID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Users] FOREIGN KEY([userID])
REFERENCES [dbo].[Users] ([userID])
GO

ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Users]
GO

thanks

Answer 1

This gets more complex when you have multiple tables involved (parent/child), but when working with a single history table, the I general pattern that I use is to create two tables, the main one: XYZ and history one: XYZ_History. I have a single insert/update/delete trigger that copies all versions into the XYZ_History table. The XYZ_History table has all columns from the XYZ table including a XYZHistoryID int identity PK, XYZHistoryType char(1) "I"nsert, "U"pdate, "D"elete, and an XYZHistoryDate datetime column, which is GETDATE(). If I need to FK a value that is the "current" XYZ value I FK to XYZ.PK, if I need to FK a point in time, I use XYZ_History.XYZHistoryID. I also have a UPDATE/DELETE trigger on XYZ_History that issues a error that you can't change the history.

it is difficult to provide any specific advise without knowing your table structure more.

Answer 2

The trigger and history tables are the usual SQL approach (as already answered). However depending on why you need to keep track of changes, externalizing state changes can be a very good idea. Unshackle you Domain (by Greg Young) has some really interesting takes on this.

In summary if knowing you changes is important for you domain/problem, you should track the actions that happened to your order, and build the current state of the order by re-applying all the changes that happened. Instead of keeping something like:

Order A with Item1 and Item 3

You store the actions:

1. Create order A
2. Add Item 1
3. Add Item 2
4. Add Item 3
5. Remove Item 2

This means that you can track all the changes (even those that where later undone). Of course this really depends on the usage you have for the historical data. Never the less the video has some really great ideas.