design question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

design question

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2002-12-05 : 11:29:04

Hi, I have a table (tblUser) containing user information. There is a new requirement where we want to track the history of five of the columns. (i.e. if the value changes, we want to record who made the change, why and what the new value is.) Originally there was only one field where we recorded a history, so I created a history table. Now since the requirements have changed, should I create five history tables, one for each field we track. Along with who made the change, we want to have a text field to record a small note. Although multiple changes could be made at once, they only want to have to supply one note. If I create 5 history tables, should each table have a note field, or is it better to have a history note table?

Another possible solution is to have one history table, which has a changeType column. This changeType column would contain the type of change (i.e. which of the fields was updated.) So, if a user updated three of these fields, there would be three records in the one table, or one record in three tables (if I used the multiple table approach).

Table:tblUser
us_key
us_name: want to track history
us_address
us_city
us_state
us_zip
us_phone
us_email
us_type: want to track history
us_billCycle: want to track history
us_billException: want to track history
us_openDate
us_suspendDate: want to track history

Table: tblUserNameHistory (for tracking username history)
hname_key
us_key
hname_name
hname_note
hname_updated
hname_updatedBy

or

Table: tblUserNameHistory (for tracking username history)
hname_key
hnote_key: key to history note table
us_key
hname_origName: would it be good to track the previous value?
hname_newName
hname_updated
hname_updatedBy

In the history table would it be better to have a start and end date (where the end date is a large date in the future, that gets updated when a new change is made), or is an updated date sufficient?

Any suggestions would be helpful.

Nic

Nic

Edited by - nic on 12/05/2002 11:34:57

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-12-05 : 11:51:56

Why not just add the extra columns you want to audit to the existing history table? That way you don't have to create additional tables and you can easily enforce the one-note-per-row rule. Having a separate table for each audited column will only make a lot of unnecessary work for you.

As far as whether you want to store both old and new values, the new values will be in the existing table anyway, IMHO I think it would be better to just store the old values. If you need to compare you can simply JOIN the history table to the regular table to get the newest ones.

The startDate and endDate columns can be added, there is an article/link on SQL Team that describes that kind of structure (do a search on "audit" and you'll find it)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-05 : 12:41:00

If the 5 fields are changing completely independently of each other, I would create 5 tables.

If between 1 to 5 fields change at a time, depending on which ones need to and it is all part of one process, then put them in the same table.

From your example, and without knowing your data, I'd break out:

Name
Type
Bill_Cyle, Bill_Exception
Suspend_Date

because it looks that may be how they are grouped. And, continuing that line of thought, do NOT store these values in the USer table, leave them in the history tables. Just create VIEWS that always return the latest if you want to access them easily.

Finally, this is a great way to audit data and store changes and you can go back in time to almost any point, but reporting can be difficult when you use a transactional model like this. You will do a lot of "get the max(Change_Date) for each employee less than or equal to the as-of date and then link back to the history table" type reports. But VIEWS or STORED PROCS can make this easier.

Keep this in mind if you keep it all in one table: If only 1 field changes, what is in the other fields? the data from the last record, or Nulls? if it is NULL, how easy it is to query the following table:

Date    Name      Bill_Cycle     Type
1/1     [Null]    1              A    
2/1     New Name  [Null]         [Null]
3/1     [Null]    3              [Null]
6/1     [Null]    [Null]         B

If they change independently, they change for different reasons and at different rates, so break them up into different tables and require a comment for each record in each table.

Just my humble advice! Good luck!

- Jeff

<edit> code tag fixed </edit>

Edited by - robvolk on 12/05/2002 13:26:10

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-12-05 : 13:32:53

Nic, here's my general approach... I prefer to keep the number of tables smaller, especially when they are tracking essentially the same type of information, and let the size of the tables get bigger. So I would either side with Rob's suggestion, or actually my first reaction was to create a more generic audit table that looks something like this:

AuditID (PK)
AuditField
OldValue
ChangeDate
ChangedBy
Comment

Or something like this. This would mean that if all 5 fields changed, you would have 5 records, but only one table. Depending on how you need to query the historical data, this could lead to some lengthy joins to get all the info you want, but it nicely packages up all the audit data. (By the way, a tip for how to do the joins with this method can be found in this article.)

------------------------------------------------------
The more you know, the more you know you don't know.

nic
Posting Yak Master

209 Posts

Posted - 2002-12-05 : 14:58:46

Thanks for your suggestions, they were all very helpful. I see the point of recording only the previous value in an audit table (since the new value is in the main customer table) but then the audit note will be out of sync. The provided note will be explaining the change, but that record will only have the previous value. (the note and the value together won't make sense.)

I think I am going to follow the one audit table approach but store the new values (with a start and end date) as mentioned in the "audit" article Rob pointed out
http://accesshelp.net/content/Report.asp?REPORT=4&PARAM_ID=31

I realize we will loose one iteration of data, but since this is a new request and we've never tracked this info before, I think it will be fine.

Once again, thanks for all your help.

Nic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources