Member Avatar for wuz

Hello everybody,

I was asked to design a history functionality for my database, which means that every record should be stored with a timestamp, comment (created, edited, deleted) and a user.

In my eyes there are 2 possibilities:

1) Store all entries in the same table, and add created and deleted column. Then I could use an insert- trigger to mark the current row with deleted is null.

Pros:

- no extra tables needed

Cons:

- very huge tables

- have to deal with history relevant aspects (affects keys have to add where deleted == null to every query). For the querys I Could use views only to get the relevant data but the axtra columns for the design remain.

2) Use an extra Table

Pros:

- clean tables

- Performance: I don’t have to query for the current entry (although deleted == null should be fast), only when the user wants to see the history

Cons:

- Many tables: As my database has to deal with many languages there is always a details_table to each original table to store language relevant data. So for example I have Person and Person_Details and I would need Person_History, Person_Details_History

- Maintenance: Any changes has to be made in the original and the History table

- Redundant data: Anytime when the user adds an entry I would copy the entry to the history table and add the history relevant information

I am not so happy with both solutions. I started with approach 1) but had to realize that my database is too complex. I already had composite keys or keys consisting even of 3 columns and a lot of relations. So due to complexity I can only work with extra tables.
I counted 19 details – tables, but I guess I have no other chance than creating 19 history tables for that.

Any comments or hints to speed up the history table creation process are highly welcome.
Big thx!

  • 2 Contributors
  • 1 Reply
  • 268 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by timothybard

Dani AI

Generated

A pragmatic balance between the two options usually gives the best long‑term maintainability: keep the live (current) tables small and fast, and push every previous version into a per‑table history store (one history table per original table) — but automate the creation/maintenance of those history tables and triggers so it is not a manual, error‑prone job.

For schema and key complexity (composite keys, multi‑column relationships) the following rules help:

  • Make each history table a full snapshot of the original row plus a small audit header (history_id surrogate PK, action, changed_by, changed_at). Copy all original PK columns into the history row rather than trying to reuse the original PK as the history PK.
  • Capture history inside the same transaction using triggers so the update/delete and the history insert stay atomic. Application‑level user identity should be passed to the session (for example set a session variable like @app_user) because MySQL CURRENT_USER() is the DB user, not the application user.

Example trigger skeleton (adapt to actual columns and engine):

-- application must set session user, e.g. SET @app_user = 'alice';
CREATE TRIGGER Person_before_update
BEFORE UPDATE ON Person
FOR EACH ROW
BEGIN
  INSERT INTO Person_History (PersonID, name, ..., action, changed_by, changed_at)
  VALUES (OLD.PersonID, OLD.name, ..., 'U', @app_user, NOW());
END;

Automation & scaling tips:

  • Use CREATE TABLE ... LIKE (or generate DDL from information_schema) to create history tables, then add the audit columns; script creation of triggers for every table to avoid repetitive work.
  • Avoid foreign‑key constraints from history tables back to live tables (prevents accidental cascade deletes). Give history tables their own surrogate PK for easier indexing.
  • Partition or archive history data by date, keep indexes only on fields you will query (changed_at, original PK columns), and consider placing history on a separate tablespace or server if it will grow very large.
  • If storage and schema drift are concerns, a single generic audit table that stores table_name + PK values + JSON snapshot/diff is a valid alternative, but it trades easy querying for simpler maintenance.

Echoing : automate and archive. Tests for trigger behavior, replication/binlog impact, and a migration strategy that applies schema changes to both live and history tables will save the most time.

Member Avatar for timothybard

I have used history tables for several database I have programmed. Fortunately, I was aware of the need for the history tables when I started the database, so I created them as I created the original tables. The history tables I use are identical to the original tables except I replace the ID field with a field called OriginalID and I add a new primary key to the history table. I track the user who updated the data and the date the data was updated on the original table.

In order to create the history tables, you can just copy and paste the structure of the tables and add a few fields or you can do it programatically. The number of history tables will probably determine which you want to do.

I agree that it can be a pain to update a table since you need to update the history table as well.

As far as redundant data... separate history tables are not any more redundant since, in your first option, you have several records with the same data except for the data that was updated and which one is current.

Lastly, if you keep your original tables in one database and keep all your history tables is a separate database and just link to the data from the frontend, then you can allow your history table database get bloated without your original data database being bloated.

If you are only required to keep the history records for a certain amount of time, then you are delete the old records to keep the database size down.

For my cases, the history files are rarely needed, so if the file does get bloated, it doesn't matter as much since I only access it when there are issues.

Some other options concerning the history files... if you don't need all the history records at your finger tips, you can backup the old history records into their own database so that the "active" history database stays small. You can also put each history table in its own database so that no single history database is that large.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.