Do you create relationships for log tables?

[JMongi]

Like it says on the tin. Mainly about list tables. I have a status list table, a level list table, etc.
And, after update I'm going to write a log record to a separate table via VBA. Is it beneficial/useful to set up relationships to the appropriate list tables? I say no because the log table is purely a snapshot of time and therefore is not technically related to anything else.

But, I am ready to be corrected

 

[Isaac]

ok i'm like 75% understanding the question here ... but if it is what i think it is, i would perhaps lean toward "no". but "it depends".

in many cases an audit/log table might be where one goes in rare situations (that shouldn't technically occur) to dig for explanations when all other bets are off, meaning that, typical/formal/primary data structures are not effectively answering the questions that need to be answered. thus if the audit/changes table depends on primary data structures, it may take away from its independence/purpose.

on the other hand, if the audit/log table IS considered a primary data structure, not a back-up or fail-safe of any kind, and will be regularly queried as a primary System of Record to see changes over time, then you might take the opposite approach, hook it up 100% to the primary data structures and make full use of keys and relationships.

is this even in the ballpark of what you were asking. maybe not, if not don't mind me.

 

[JMongi]

In this usage case, the primary question would probably be "What moron gave that guy the access he used to screw things up...?" So, only an occasionally accessed changelog for user level access to the database.

 

[Pat Hartman]

You wouldn't relate the log tables to the primary tables but the log tables themselves might have relationships. I would enforce relationships among the log tables to prevent accidents more than anything else. The log tables should never be updated, ever. Only appends should be used to add data to a log. But having a relationship protects against certain types of mistakes made by your successor, not by you since you know the overview.

 

[JMongi]

@Pat Hartman - I only partially follow you. What type of relationships would exist between log tables?

 

[The_Doc_Man]

I had a case where there was a relationship for log tables that allowed me to place a "consolidated" log table that tracked events for every form and table, mostly so that I didn't have to spell out the names of all my forms, tables, queries, reports, etc. So I had an "identity" table that allowed me to use a number to identify some things. My fields were: EventTime, EventObject, EventActor, EventDescription. The EventObject was just based on a list of objects. Derived it from the appropriate MSys table. The EventActor was the internal user ID of the person who was using the FE at the time. By doing this, I saved a maybe 15-20 bytes per event. However, because of the frequent logging that I did because this was a Navy project for which auditing was a "MUST" - I saved a few thousand bytes per day. It added up because I probably archived 80K-100K events per month. Every couple of months I would archive the oldest entries as part of my regular maintenance regimen.

In case anyone asks, the only time a query would get logged is if it returned an error other than "no data" - which I could detect because I had extensive error trapping in place. Once I got the "automatic query builder" fixed (essentially a six-level cascading combo box), those errors stopped happening.

 

[Pat Hartman]

Doc's explanation is one of the situations I was thinking of but also if your log has essentially the same schema as the primary database, you have to consider how you are going to make use of the data you are logging. What happens if the user wants to view things as they happen? You might want to log the entire parent chain when some child record changes in order to easily produce a coherent report of what happened. Otherwise, you would need logic for how to pull out the "parent" from the log or from the primary table.

So, before you commit to a logging method, do some thinking on what you are going to do with the data. It is pointless to save data out of context.

 

[JMongi]

@The_Doc_Man and @Pat Hartman - Thanks for the real world examples and food for thought.