The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Data Modelling - How you do this.

This is somewhat more technical.  But I am sure anyones who work with databases extensively comes across the situation.

For simplicity, say I have a transactions table (e.g. personnel shift records) and each record is denoted with a current status (e.g. reviewed, pending, approved, locked).  Furthermore, I would like to retain a history audit of the status changes.  At the same time, I want to be able to quickly query the current status of records.  I see two ways of handling this:

<1> A status field on the main transaction table in conjunction with a log history table.  When ever the status field changes write a entry into the log table.  The main benefit is it makes querying records for specific status easy.  The drawback is that you cannot be assured that the current status field jives with the most recent record in the transaction history.

<2> Rely only on a log history table.  The major drawback here is it makes querying and reporting (i.e. with Crystal Reports) difficult.  For instance, it becomes more difficult to write a query to show all pending records.

How would you do it?
Richard Gardner Send private email
Wednesday, July 18, 2007

"The drawback is that you cannot be assured that the current status field jives with the most recent record in the transaction history."

No transacions in this database?
Rich Fuchs
Wednesday, July 18, 2007
Are you planning on having your end users write queries? There are other problems with that, besides the difficulty of the queries.
Steve Hirsch Send private email
Wednesday, July 18, 2007
I say <1> as well. Think of the 'current status' field as a 'cache' of the status change record. It should be easy to run frequent checks on whether the fields agree. You could do it at record update time, if you had the spare CPU, or overnight.
DJ Clayworth
Wednesday, July 18, 2007

Thanks for the note.  Yes - we could write a routine to periodically go through and re-update.  The proper way to handle this is to ensure that triggers exist on the table to do this processing.  But right now we are relying on the client application to handle this business rule.  Our application runs against MySql, SQL Server and Oracle via ODBC and ADO technologies.  So we tend to put most of the business rules in the client instead of where they should be: on the database.
Richard Gardner Send private email
Wednesday, July 18, 2007
If you're doing the login in the application then make sure the status change is written to the database from only one place, and always use that routine to do the update.  Then you can more easily ensure that both the log table and the status field are being updated.

Barring using a trigger in the database, that will really be your only option aside from the periodic syncing mentioned above.
Wednesday, July 18, 2007
you could also implement triggers on the dbs that support them.
Totally Agreeing
Wednesday, July 18, 2007
It's usually done with version tables.  For example, you can have one table called employee (primary key emp_id) and another table called version (primary key emp_id + emp_vid).  Everything about the employee that doesn't change (e.g., hire date) is stored in the employee table.  Everything that can change is stored in the version table, with effective change date.  When changing things, add new record to the version table and update emp_vid in the employee table accordingly.
Note how no piece of information is stored in more than one place, and to get most recent status you only need one join.  You can then create views to simplify reporting.  If you run into performance problem, materialize views.
Jeff Zanooda
Wednesday, July 18, 2007
There is also <1.5> way - log table plus a view. Thus you can make queries from CR, and have the status allways current. Still has it's own disadvantages, but needs to be listed for the sake of completness.

Wednesday, July 18, 2007
I tend to think of this differently depending on whether the log records are generally considered first-class entities in the application - i.e. are they an implementation necessary to meet an end-user requirement or simply a back-end audit function which does not need a user interface.

Even if a user interface is required, if it does not need to be fancy, I give a no-frills view on the raw audit trail and the audit trail is trigger-based and similar to the form created by those auto audit log stored procedures floating around for SQL Server 2000 and 2005.  Basically, the user chooses a record, says "Show History" and sees things like:
DateTime, Field, BeforeUpdate, AfterUpdate
YYYY-MM-DD HH:MM:SS, Status, Active, Closed

If a full blown user interface is required (autopayment credit card history - a given card has multiple payment schedules, some are active, some inactive, etc), then the temporal changes or status change management is built-in to the primary entity table(s).  Obviously such a user interface is richer, with controls enabled and disabled, etc.
Cade Roux Send private email
Wednesday, July 18, 2007
I'd use a trigger personally, and put changes into some sort of log table.
Tuesday, July 24, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz