[thelist] Database history - handling references to rows whichdon't exist any more?

Chris Anderson Chris at activeide.com
Thu Apr 23 18:58:40 CDT 2009


> If the user adds or deleted a new item to the database (among other
> actions)
> using the application, we store that information in our history table.
> 
...
> Therefore we are saving the primary key of the items in our history
> table
> like so:
..
> 
> This is because I am building links pointing to the items on the fly,
> i.e.
> if there IS an item_id, then I pull the item info from the database
> using
> that item_id and then output HTML like

1.Do you need to show the previously deleted items?
2.Do you need to show the state of a record at any point in time?

If 1 but not 2, just add a "IsDeleted" field and when reading, include
"IsDeleted = 0" to the where clause
(It's a good idea to use a view to do this and always select from the
view - then you'll not forget)
Use the isDeleted field to determine how you present the output

If 1 and 2 create a mirrored schema eg

TableA:
	refid int not null identity(1,1),
	someField int not null,
	someOtherField int null

TableAAudit:
	auditId int not null identity(1,1),
	changeDate datetime not null,
	changeType int not null (or char(1) not null if prefered)
	refid int not null,
	someField int not null,
	someOtherField int null	

where:
auditId is just there to provide a point of reference and uniqueness
changeDate is the date/time of the change
changeType is the type of change (create, update, delete)
refid is the PK of the record in TableA
someField is the new value of someField (null if record deleted)
someOtherField is the new value of someOtherField (null if record
deleted)

Note that even though someField was not null in TableA, it's nullable in
the TableAAudit table. This is to allow you to store null for when the
record is deleted.

Finally add a trigger to TableA and store the new data in TableAAudit

To find the state of a record at a particular time, simply find the
latest record in the audit table before that time (i.e. SELECT TOP 1
.... ORDER BY changedate DESC)

The fun starts if you want to tie together changes that occur within a
single transaction (eg adding an order header and 5 lines) and reporting
those as a single atomic operation.
(It's possible based on the latter approach, but a little more involved)

Chris



More information about the thelist mailing list