Database structure to track change history

Question

I'm working on database designs for a project management system as personal project and I've hit a snag.

I want to implement a ticket system and I want the tickets to look like the tickets in Trac. What structure would I use to replicate this system? (I have not had any success installing trac on any of my systems so I really can't see what it's doing)

Note: I'm not interesting in trying to store or display the ticket at any version. I would only need a history of changes. I don't want to store extra data. Also, I have implemented a feature like this using a serialized array in a text field. I do not want to implement that as a solution ever again.

Edit: I'm looking only for database structures. Triggers/Callbacks are not really a problem.

Answer 1

I'd say create some kind of event listening class that you ping every time something happens within your system & places a description of the event in a database.

It should store basic who/what/where/when/what info.

sorting through that project-events table should get you the info you want.

Answer 2

Are you after a database mechanism like this?

   CREATE OR REPLACE TRIGGER history$yourTable
        BEFORE UPDATE ON yourTable
        FOR EACH ROW
        BEGIN
         INSERT INTO
          history
         VALUES
          (
          :old.field1,
          :old.field2,
          :old.field3,
          :old.field4,
          :old.field5,
          :old.field6
          );
        END;
    /
    SHOW ERRORS TRIGGER history$yourTable

Answer 3

I have implemented pure record change data using a "thin" design:

RecordID  Table  Column  OldValue  NewValue
--------  -----  ------  --------  --------

You may not want to use "Table" and "Column", but rather "Object" and "Property", and so forth, depending on your design.

This has the advantage of flexibility and simplicity, at the cost of query speed -- clustered indexes on the "Table" and "Column" columns can speed up queries and filters. But if you are going to be viewing the change log online frequently at a Table or object level, you may want to design something flatter.

EDIT: several people have rightly pointed out that with this solution you could not pull together a change set. I forgot this in the table above -- the implementation I worked with also had a "Transaction" table with a datetime, user and other info, and a "TransactionID" column, so the design would look like this:

CHANGE LOG TABLE:
RecordID  Table  Column  OldValue  NewValue  TransactionID
--------  -----  ------  --------  --------  -------------

TRANSACTION LOG TABLE:
TransactionID  UserID  TransactionDate
-------------  ------  ---------------

Answer 4

One possible solution is storing a copy of the ticket in a history table with the user that made the change.

However, this will store alot of extra data and require alot of processing to create the view that Trac shows.

Answer 5

As far as not storing alot of extra data, I can't think of any good ways to do that. You have to store every revision in order to see changes.

Here is one solution I have seen, although I'm not sure if it's the best one. Have a primary key, say id which points to a particular revision. also have ticket_number and revision_date fields. ticket_number does not change when you revise a ticket, but id and revision_date do. Then, depending on the context, you can get a particular revision, or the latest revision of a particular ticket, using groupwise max.

Answer 6

I did something like this. I have a table called LoggableEntity that contains: ID (PK).

Then I have EntityLog table that contains information about changes made to a loggableentity (record): ID (PK), EntityID (FK to LoggableEntity.ID), ChangedBy (username who made a change), ChangedAt (smalldatetime when the change happened), Type (enum: Create, Delete, Update), Details (memo field containing what has changed - might be an XML with serialized details).

Now every table (entity) that I want to be tracked is "derived" from the LoggableEntity table - what it means that for example Customer has FK to LoggableEntity table.

Now my DAL code takes care of populating EntityLog table everytime there is a change made to a customer record. Everytime when it sees that entity class is a loggableentity then it adds new change record into entitylog table.

So here is my table structure:

+------------------+           +------------------+
| LoggableEntity   |           | EntityLog        |
| ---------------- |           | ---------------- |
| (PK) ID          | <--+      | (PK) ID          |
+------------------+    +----- | (FK) LoggableID  |
        ^                      |      ...         |
        |                      +------------------+
+------------------+
| Customer         |
| ---------------- |
| (PK) ID          |
| (FK) LoggableID  |
|      ...         |
+------------------+