need help with db that keeps history
-
sushila,
"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."
What I was thinking of doing is storing tblDocument.ID, tblAddress.ID, tblContact.ID, tblStore.ID etc in the tblStatus.ObjectID. So I think you and I are in the same frame of mind here. I just named it ObjectID to be generic.
"2) From what I can understand - there are 3 actions that can be performed - insert, update and delete..."
those are the most common part. I am going to put all the other actions in this list also such as: "FROM FINANCE TO LEGAL" to keep track of when a document goes from finance to legal, "VERIFIED" for when a document is looked over and verified, "APPROVED" for when an application is approved, "NEW ARRIVAL" for when a new employee is hired, "FROM LEGAL TO FINANCE" etc. So there will be a lookup table with all these and the user clicks on the proper button to perform the corresponding action. what do you think of this idea?
"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 ?!"
when inserted, ex:
tblContact
ID storeID FirstName LastName Title
10 99 john doe owner
in tblStatus there will be an entry like:
tblStatus
ID ObjectID UserID ActionID Date
so if its modified by user 100 then:
tblContact
ID storeID FirstName LastName Title
10 99 john doe owner
11 99 jane doe owner
in tblStatus there will be an entry like:
tblStatus
ID ObjectID UserID ActionID Date
x 11 100 MODIFY 7/2/05
so when its delete by user 100 then:
tblContact
ID storeID FirstName LastName Title
10 99 john doe owner
11 99 jane doe owner
12 99 jane doe owner
in tblStatus there will be an entry like:
tblStatus
ID ObjectID UserID ActionID Date
x 11 100 MODIFY 7/2/05
x 12 100 DELETE 7/5/05
so when a user searches for contact history for a store (id = 99) then they will see:
ID storeID FirstName LastName Title ActionID Date
10 99 john doe owner
11 99 jane doe owner MODIFY 7/2/05
12 99 jane doe owner DELETE 7/5/05
I outer join tblContact.ID to tblStatus.ObjectID. Also ActionID will really have the ID of those actions. I put the action description of the actions here to be clear. Also the default view would be to show last to first.
"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"
I think i will need to re-copy the row contents for the delete as it will be treated as a modify but with a different action. also if I dont repeat the contents then and just update the modify to delete then i will loose the last time it was modified. The main thing i am trying to do is not have to have a status field(ACTIVE/INACTIVE) in every table and use the tblStatus for all action status's.
"f this doesn't cover everything, could you please post:"
i think this kind of covers everthing. I really appriciate all the suggestions and help you have given me. Your ideas + co-workers + very little from me + writing out all the responses has helped me flush out the design pretty good.
i thank you for all the time you put in to help me.
-
"So there will be a lookup table with all these and the user clicks on the proper button to perform the corresponding action. what do you think of this idea?"...
sound idea!
"also if I dont repeat the contents then and just update the modify to delete then i will loose the last time it was modified."...
Tarique - I think there was a disconnect here...I was suggesting you keep a separate "deleted table" that would have objectID, tableID, UserID, DeletedDate instead of repeating the contents of the row....
-
NOT "modify the update to delete"...for that won't give you your history preservation...
Let me know if you have any more questions - s
**ASCII stupid question, get a stupid ANSI !!!** -
Thank you for all your help and ideas.
"Tarique - I think there was a disconnect here...I was suggesting you keep a separate "deleted table" that would have objectID, tableID, UserID, DeletedDate instead of repeating the contents of the row...."
I think thats a great idea! i will implement that. Thankx.
I am going to write up the documents to show legal and finance. Hopefully you have helped me flush out everything.
thanx again.
-
Anytime Tarique...I enjoyed the challenge of thinking it out...it was fun!
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply