Any Database Table Should Have All of These

Okay, maybe not all of them. Lookup tables can be excused, although I think that they could even benefit from these extra columns.

  • ID: Data should always be uniquely identifiable
  • Standard Auditing Columns
    • Created Date: So we can track the age of the data
    • Modified Data: So we can track the last usage of the data
    • Modified By: So we know who edited the data last
  • Tag: Logical grouping of data, comma delimited

The last one is a very obscure one I know. Let me explain:

I have worked on and designed a few systems where we would build certain information into the data model to cater for categories, sections, subsections etc. Mostly this was to be able to group the data in some form that was predominantly used only by developers. Examples would be:

  • Having data grouped by year or semester at a Higher Education institution
  • Having a hierarchy for users and roles
  • Creating calendars in an hierarchy or events having parents, etc
  • Further subdivision of a tutorial class into groups of six
  • Recurring events and allowing for modification of one to update the rest
  • Many more…

I must admit, that for the examples above, I still want to embed this into structure and most probably would. What was nice about the recent system that I have recently dug into, is that they through out all of this. They decided to not logically group data, as I am sure their clients did it differently for each institution.

They decided to tag their data, only through developer interfaces. This means that when I upload data or update data programatically, I can tag it. If I make a mistake, I delete that tag of data. It can be linked to all the data e.g. users, course, groups, roles, etc, that I did the update on. I can link data that is unrelated. I can make connections in the data where I need them. It is extremely handy way of doing it IMHO and it is unseen and unknown by the user.

This might be trivial to some… I think that this is a nice feature to have in database design, as it helps with scope creep as well as changing requirements, integration, enhancements, etc.