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

[Jay Turley]

Thanks, Chris! For now it's just #1, so the isdeleted field will work
perfectly.
After treading everything you wrote, it reminded me of an article I had read
once about journaling databases, where you keep track of every change made
ever! I'm SO glad I don't have to create something like that. Whew!

-Jay

On Thu, Apr 23, 2009 at 4:58 PM, Chris Anderson <Chris at activeide.com> wrote:

> > 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
> --
>
> * * Please support the community that supports you.  * *
> http://evolt.org/help_support_evolt/
>
> For unsubscribe and other options, including the Tip Harvester
> and archives of thelist go to: http://lists.evolt.org
> Workers of the Web, evolt !
>