Normalizing audit log

[reillc01]

Hello. A question about designing relationships with an audit log.

I am setting up a HR db. I want to create a table that records actions performed to employees. I am noticing that my relationships are growing more complex and don't want normalization problems long term.

TblEmployees
TblLocation
TblActions
Tbljob
TblLog

The goal is for the log to track what was done, to what employee, and in which location. Later I want to see how many new hires came fom store x, or how many raises emloyee y has had, or when did employee z get promoted.

That puts several tables dependant on two others. Is that a problem?

Eg tblemployess > tbllocation < tblLog

Should I create a seperate table for each action? I.e. new hire, termination If so, how do I deal with the combo box on the form? Since I have a single actions table, my data entry form gives me a combo box with all possible actions. If I split up that table, how do I make the data entry form easy?

thanks in advance!!!

 

[dportas]

Normalization is concerned with dependencies within tables, not between them. It's difficult or impossible to give specific design advice in a forum without being familiar with your particular situation. Having said that, nothing you've said seems to indicate anything "wrong" with your design.

Good database design has nothing to do with user interface design. I suggest you separate your concerns about implementing a combo box and deal with that after you've designed the right data model.

 

[MarkK]

I don't see a clear distinction between your action table and your log table. Why is the action table not enough for you to ...

track what was done, to what employee, and in which location

???

 

[reillc01]

thanks for taking an interest. Let me back up a bit:

tblEmployees - name, address, wage, etc
FK locations
FK Jobs

tblLog - date, wage change, notes
FK Employee
FK Jobs
FK Locations
FK Actions

tbl actions - new hire, termination, raise, etc

tbljobs - crew, manager, etc

tbl locations - pullman, grove city, clearview, etc

From a design standpoint, does this seem like a good plan? This is my first attempt with Access. I have always used Excel, so working in three dimensions, so to speak, is a new challenge. The log table seemed like a good way to collect all of the actions performed on an individual employee, so I can track hire date, termination dates, wage changes, etc. Perhaps someone more experienced than I, has a different solution. I just want to get it right from the start. The question I previously asked, alludes to a different organizational solution. Rather than have one log table with different actions, would it be better to create a table for each action? Like:

tblNewHire
tblTerminations
tblWageChanges
etc
Each would have
Date
Notes
FK Employee
FK Jobs
FK Locations

Thanks again for lending your experience.

 

[gemma-the-husky]

there are a couple of different concepts here

one option is to store a history of individual fields - typically say with a database concerened with sale prices, you would store a history of prices, to be actively used in determing the appropriate price to be charged, based on a given sale date, and here you might store details of the date it changed, who processed it, who it was agreed with, and so on.

now, with some fields you may not be bothered about the history - say a contact changes his phone number. Are you bothered enough to store the fact that the phone number changed, when it changed, and who processed it - i suspect not

so, one point is that you probably need to be selective about the fields you audit.

the other thing is that an audit log is somewhat different to a relational table. The relational structure is concerned with being able to derive functionally important data at a point in time.

An audit trail may be the same, but is often just concerned generally with merely maintaining a record of which data changed and who changed it, for record purposes.
So often an audit record would be hard to use for data processing, or not intended to be used for data processing.

-----------
this is more in general terms - i havent really considered your proposed data tables - but i dont think you would store separate actions in separate tables. I dont think you would even store new hires, and terminations in this way.

the start date, and termination dates, can only ever have 1 value, and should be fields (attributes) of the employees table. The payrate is a bit different. You would (could) store the current pay rate in the employee table, and store previous rates in an audit history log - or you could store all the dated payrates, in a linked history table - depends what you want to do with old rates, to a great extent. It might be enough to just store the current pay rate, and the last payrate in your table

althgough this isnt normalised, its flatter, and easier to handle programmatically - so like a lot of things, it depends on exactly how far you want to take it.

 

[reillc01]

Thanks for the reply Dave/Gemma,

If I hear you correctly. The values stored in that table, shouldn't be relationally linked to any other tables that are updated over time because the audit entry should not change. I.e. I don't want the entry for the starting wage to change when I change the current wage. So while I might get the information for the starting wage from the wage field in the Employees table, it is just entered as a number, not as a lookup to the same.

You also bring up a good point with the hire/fire values and programability. We employ lots of younger folks who do come and go with the seasons, so they can have multiple hire dates and termination dates. I need to be able to keep track of all those details. A use case is that somebody worked with us for the school year went home over summer break and missed the annual review cycle in august. They come back in the fall and didn't get a raise in August because they were not a current employee at the time. So I want to be able to go back through their history and see that their last raise was on x date, and that they are due for an increase. When it comes time to get that information, having a single log would allow me to expand the record in the employee table to see all the related entries in the log
hire date
discipline
raise
discipline
termination
rehire
raise
etc.
But a query could do the same thing if all my actions were split into multiple tables. So thinking ahead to all the programming issues, I haven't considered yet, which is better? The multiple table solution or the single log solution. It sounds like you would prefer the flatter log solution from a programming perspective?

thanks! jonathan/Beau&Brie

 

[MarkK]

And welcome to the development process!!!
Mostly I find I move back and forth between the data inputs and outputs and the developing table structure I have at the moment and keep tweaking it with test data.
And from what you've written it doesn't seem you're really trying to implement an audit system, which is sort of like keeping data about the update history of the data itself.
I think you need an Employee table, a Employment table--where you record hires and fires, and a Discipline table. To me each of these tables will maintain distinct data.

[B]tEmployee[/B]
EmployeeID (PK)
Name
Wage

[B]tEmployment[/B]
EmploymentID (PK)
EmployeeID (FK)
Hired
Unhired  'so your current emplyees are those where this field is null
ReasonForUnhiring

[B]tDiscipline[/B]
DisciplineID (PK)
EmployeeID (FK)
Date
etc...

I've put a wage field in the employee table. If you use that data in the system somewhere, let's say you keep track of employee's hours worked in a Labour table, then record the Employee's rate there for each dated instance of time that EE worked. That then serves as your rate history for that EE; the actual payroll record itself.
And there's more, like do your Jobs only occur at specific Locations? Can one Job occur at multiple Locations? Does tracking locations actually add value to the system at all?
Hope this helps...

 

[The_Doc_Man]

There are a few reasons for keeping an audit log.

1. A business procedure or fiduciary regulation requires it as a matter of security. In that case, the audit log is not related to anything in the formal sense. It is like a paper log book in which you dated an entry and wrote something in ink.

2. You want to be able to roll back to a given time. In that case, you really need to keep point-in-time backups and keep transaction records. In which case you would back up before the date in question and roll FORWARD to your point in time. But that is not the same as #1.

3. You want to use the log for some kind of accounting. Like perhaps finding out who is using the database for something useful. As a sort of time-accounting thing or workflow efficiency thing.

When talking about the audit log, first decide which kind it is before you decide what to keep in it. If it is really a security log, you don't care if it links to anything except the name of the person making the change. If it is a transaction and rollback log, then it needs to have pointers to everything. And if it is an accounting log, it might really represent a parallel table to some of your other tables, related only in general but with it's own data set.

First decide what you need. THEN decide how to build it.