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 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 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