5

I'm working on an application where I need to save basically :

  1. CreatedBy (FK to the person table)
  2. CreatedDateTime
  3. UpdatedBy (FK to the person table)
  4. UpdatedDateTime

Right now I have these fields in all the tables in my database. But I feel like there should be a better way to do this.

  • 1
    Include those columns in each table, and then maintain them using code or database triggers. – Robert Harvey Oct 4 '17 at 22:28
  • 1
    Looks absolutely fine to me. As a DBA on extremely large databases we always had created and modified dates populated in our data factories. If > 1 user can update these tables, it makes perfect sense to include the person PK too. – Robbie Dee Oct 4 '17 at 22:37
  • 1
    Question : the createdBy/updatedBy fields, correspond to an authenticated users against your application or the db's user connection ? If the latest you won't be able to handle it with triggers. – Walfrat Oct 5 '17 at 7:48
  • @Walfrat: several databases support the concept of a session id. Storing the pair "Session_ID, Person_ID" in some table immediately after the connection is established makes it possible to handle even that scenario with triggers. – Doc Brown Oct 5 '17 at 7:59
  • @DocBrown when you say a session_id, do you mean within one maintened connection of the database ? Or would it work for instance against a web based application where connection are distributed among user randomly ? – Walfrat Oct 5 '17 at 8:07
6

It is a common an perfectly valid way to do it. Some major ERP packages, like for example SAP, do exactly this.

There are two issues to address:

  • Ensure that these fields are maintained reliably. They are useless if a rogue UPDATE can change the row without the fields being updated (hints: either db triggers as suggested in Robert's command, or via the database gateway layer if db access is appropriately protected).
  • Ensure that these fields can't be tampered with if the audit trail is critical and access to db can't be completely controlled. This is more delicate, but could be achieved using an additional hash (or better, a signature) computed with all the relevant data.

There is an alternative to having the fields in all the tables. You could put all your 4 audit trail fields together with an object id (table+unique id) somewhere else. It could be another table (you could restrict access to it) or in a file acting as audit log (that could be out of range of the dba to ensure segregation of duties, but that would be far less convenient to use), or both.

  • Thank you. I will stick with this then. One last thing, My application is a multi tenant application. So I have a "Company" table. Each person should be assigned to a company. In my initial data seed It was kind of wired. So company had to be inserted before I insert the person, but Company also had a FK to person for CreatedBy. :) I made the FK nullable and updated the table later. Couldn't find a way around this. – Kasun Koswattha Oct 5 '17 at 12:35
  • 1
    @KasunKoswattha This could deserve a question of its own ;-) In your multi-tenant scheme, is a user-id local to the company (primary key= company code + user id) or global (i.e. primary code = unique user id; user company is only for the authorization check) ? In the first case, you'd need the additional company code for CreatedBy & ModifiedBy. In the second case, you wouldn't need it: your intent is not to trace to which company the user was assigned when he did the transaction, but to uniquely identify the user. Last question: can a user (e.g. admin) be assigned to several companies ? – Christophe Oct 5 '17 at 18:14
5

Your solution is not bad. Here is an alternative solution (not better or worse in general, just different). For many systems, it is common to have a uniform convention for the primary keys of the following form:

  • no composite PKs

  • each PK has the same datatype (or at least fits into a known datatype)

If your system follows this policy, then you can store the above information in a separate audit table with columns

  • Tablename (String)

  • PKValue (same type as the primary keys)

  • Operation (values representing Create or Update (or maybe even Delete)

  • TimeStamp

  • OperationBy (FK to person table)

Some advantages of this solution:

  • more normalized,

  • no need to add four extra columns to each of the existing tables

  • allows to keep track of delete operations

  • the audit information is in one place, which may allow to fine-tune specific storage parameters for this table

  • a query over the full audit table becomes easier

  • access rights to the audit information is independent from the access rights to audited tables

Disadvantages:

  • you need this convention for the PKs

  • each update or create has now to modify two records instead of one

  • if records from the main tables are deleted, audit information is not deleted automatically with them

  • a query for the last change of a specific record now needs a join operation

  • access rights to the audit information is independent from the access rights to audited tables (did I mention this beforehand :-)?

So as you see, both approaches differ in detail, and you should verify which one fits best to your requirements.

  • I tend to favour something like this, but I have separate fields to hold different types of Primary Key (usually integer, string and GUID). – Daveoc64 Oct 5 '17 at 15:14
  • @Daveoc64: if there are no composite keys, using a large enough string field might work even then. – Doc Brown Oct 5 '17 at 16:08
4

If you only need to see the last change (and who made it), then this is perfectly adequate.

If you need to see a history of changes, then you need to duplicate this data into other tables, preferably using triggers so that no change "slips through the net".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.