need help with db that keeps history
-
Hello,
Here is the scenario:
I have to design a database application that requires history being kept anytime something is created, modified or de-activated. Ex:
I have a document table:
tblDocument
docID docName docLocation docVer
1 temp.txt c:\tmp 1.0
everytime it is modified I have to keep history of previous info. So if I modified the docLocation to c:\abc then history would be following.
tblDocument
docID docName docLocation docVer
1 temp.txt c:\abc 1.0
tblDocumentHistory
docID docName docLocation docVer action date
1 temp.txt c:\tmp 1.0 MODIFY 1/1/05
this way combining tblDocument and tblDocumentHistory would give me a full picture. The issue is if I delete temp.txt so tblDocument and tblDocumentHistory looks like:
tblDocument
docID docName docLocation docVer
tblDocumentHistory
docID docName docLocation docVer action date
1 temp.txt c:\tmp 1.0 MODIFY 1/1/05
1 temp.txt c:\tmp 1.0 DELETE 1/2/05
then users looking for temp.txt will not find it unless they go into history. This is not very user friendly. Other option is:
tblDocument
docID docName docLocation docVer docStatus
1 temp.txt c:\abc 1.0 ACTIVE
tblDocumentHistory
docID docName docLocation docVer docStatus action date
1 temp.txt c:\tmp 1.0 ACTIVE MODIFY 1/1/05
then when "de-activated" then the tables look like:
tblDocument
docID docName docLocation docVer docStatus
1 temp.txt c:\abc 1.0 INACTIVE
tblDocumentHistory
docID docName docLocation docVer docStatus action date
1 temp.txt c:\tmp 1.0 ACTIVE MODIFY 1/1/05
1 temp.txt c:\tmp 1.0 INACTIVE MODIFY 1/2/05
this way they can still get to temp.txt and they know that its been "de-activated". this is just one table. If I decide to design this way then I have to have a status column in every table and a history table for every table. this gets really messy. How can I accomplish this in the most cleanest and most efficient way???
any thoughts will be appriciated/
-
it looks to me like you are duplicating what Visual SourceSafe does. Can you force everyone to use SourceSafe in order to manage your documents and the changes that occur to them?
If possible, it's better to use that then re-invent the wheel.
if you can't make them use it directly, you could write an application which uses the the Source Safe API in order to check out and update files, and give you the flexibility to query a database for your "inactive" status.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
the example of Document that I gave is just one part. similer will apply to address records, contact names. phone/fax etc numbers, and other company related information. If it was just about documents then source safe would have been enough. almost like an audit system such as source safe but with more stuff for our needs.
-
I'm not sure how often tables are added to the database and how many of these tables will need to be audited.
I don't know if there're common threads between all the tables that require an audit trail...meaning do they all have version, location, name etc..based on the limited information, this could be one of the ways:
1) Have a status lookup table with :
StatusID & StatusDesc - here you could have:
1, Active
2, Modify
3, Deleted
4, Inactive....and so on
2) Have another lookup table for all the tables that will require the history trail:
TableID, TableName
Here you will have:
1, tblDocument
2, tblAddress
3, tblNames
4, tblCompany....etc.
3) Then you have the actual (master) history table that will store the history of ALL tables....
TableID, StatusID, ActionDate, Version......other columns
4) You can then create a view for your reporting/tracking needs.
**ASCII stupid question, get a stupid ANSI !!!** -
sushila,
I was thinking about/discussing the situation last night. The solution I, with help from co-workers, came up with is we will keep the tables as is and have a status table (ObjectID,UserID,StatusID,ActionID,Date). each table will hold history: no updates, just inserts and each action also inserts a record in tblStatus. this way we only show most recent active stuff.
-
Okay - but that's a different requirement than what you outlined in your original post where the audit trail tracked ALL activities including delete, modify etc...
Ultimately, it's WHATEVER keeps the client happy!!!
**ASCII stupid question, get a stupid ANSI !!!** -
what i am going to do is:
tblDocument
ID docID docName docLocation docVer
99 1 temp.txt c:\abc 1.0
when client changes c:\abc to c:\xyz then
tblDocument
ID docID docName docLocation docVer
99 1 temp.txt c:\abc 1.0
100 1 temp.txt c:\xyz 1.0
tblStatus
ObjectID UserID ActionID Date
100 999 MODIFY 1/1/05
when client changes version to 1.1 then
tblDocument
ID docID docName docLocation docVer
99 1 temp.txt c:\abc 1.0
100 1 temp.txt c:\xyz 1.0
101 1 temp.txt c:\xyz 1.1
tblStatus
ObjectID UserID ActionID Date
100 999 MODIFY 1/1/05
101 999 MODIFY 1/2/05
this way joining tblDocument and tblStatus by tblDocument.ID and tblStatus.ObjectID I will know all the actions and I can use tblStatus as a generic audit history table for all other tables. Let me know what you think.
-
I don't think I understand the original requirements...
1) Are all actions going to be only "MODIFY" ?!
2) What are the DDLs of the other tables that you want to audit ?! Could you post at least a couple of them ?! Do all of them have ObjectIDs ?
3) Could you please outline the requirements in detail for they seem to have changed over the posts?!
**ASCII stupid question, get a stupid ANSI !!!** -
the requirements are:
there are processes, documents, contacts, addresses, company info, stores info, employee info, insurance for each, 401k info for each etc. processes can have multiple documents attacthed to it. stores can have multiple contacts with multiple addresses etc. changes in any of the above need to be tracked. My original way of thinking is that every single table needs to have a corresponding history table to capture data before update OR delete (but delete really means de-activate since nothing can be deleted. So it will act like modify).
the Document table is the same as above. here is the address table:
tblAddress
ID Address1 Address2 City State Zip
30 123 pine st paris ct 12345
so when this is modified:
tblAddress
ID Address1 Address2 City State Zip
30 123 pine st paris ct 12345
31 123 nut st rome ct 99999
the status table would have
tblStatus
ObjectID UserID ActionID Date
31 999 MODIFY 1/1/05
tblAddress.ID is the ObjectID just like tblDocument.ID was the objectID before
So instead of a corresponding history table for each table I am storing the history in the same table and using the status table to capture the event (MODIFY,DELETE) related info.
hope this makes it more clear.
-
Some first thoughts....
It seems that the ID field in each of these tables is IDENTITY...?!?!
When a specific row is modified, how do you know which one it is ? In your example from tblAddress, how do you know that the address in ID 31 is a modification of the address in ID 30 ?! (the document table has a docID....)
If delete means deactivate then that is what the status should say - otherwise, how will you ever differentiate between delete, modify and insert ?!
**ASCII stupid question, get a stupid ANSI !!!** -
It is IDENTITY and I am using
SELECT @@IDENTITY AS NewID
right after I do the insert to get the ID. this way this ID is my ObjectID.
"When a specific row is modified, how do you know which one it is ? In your example from tblAddress, how do you know that the address in ID 31 is a modification of the address in ID 30 ?! (the document table has a docID....)"
in the user interface when the selects a record to modify, i will get the ID of the record they chose. uisng this ID i will get the necessary information.
"If delete means deactivate then that is what the status should say - otherwise, how will you ever differentiate between delete, modify and insert ?!"
you are right, it should be de-activate.
-
Tarique..."selects a record to modify, i will get the ID of the record they chose. uisng this ID i will get the necessary information...."
- which column in your address table indicates this ID ?!
Likewise....what about all the other tables that will be audited - do they all have unique IDs like you docID ?!
**ASCII stupid question, get a stupid ANSI !!!** -
Each table has an Identity ID which I guess i didnt show. My mistake on that. I tried to keep the post to a minimum.
-
Don't do that, the more precise and accurate info we have, the better answer we can provide.
-
Tarique...couple of things:
1) Your objectID is not useful at all...you should use docID, AddressID etc. since this is the only ID that uniquely identifies your rows.
2) From what I can understand - there are 3 actions that can be performed - insert, update and delete...
a) when inserted, it is a brand new record so the status is ACTIVE (??) - in this case you don't do anything - just mark it as ACTIVE
b) when modified it becomes MODIFY (now this can happen several times over a period of time - right ??) - each time, you repeat the contents of the entire row to preserve history.
c) when deleted - you don't really do anything - just mark it as INACTIVE or DELETED ?!
3) My initial feelings are that you keep all info in the table itself - meaning userID, ActionDate and statusID...thus the # of rows for each record will be the initial inserted one plus the # of times that it is modified.
4) You could have a lookup for status and table list as described in my first response - and this could be used to keep track of all the deleted records from each table - for when you delete something you don't have to repeat the contents of the entire row just to mark it as INACTIVE - thus you could have a deletedRecords table (or some such) that would have (eg: documents table) - docID, TableID, UserID, DeletedDate
If this doesn't cover everything, could you please post:
a) any other status types that the business rules may have ?!
b) a list of all tables that would need this audit trail and the names of the columns that would be modified ?!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply