What are the best practices for designing DB Tables

Question

We're building our DB design (using PostgreSQL) and for (almost) every table , I have the following columns

CREATE_TIMESTAMP TIMESTAMP,
CREATED_BY       VARCHAR(25),
modified_TIMESTAMP TIMESTAMP,
modified_BY       VARCHAR(25),

I am also using Audit tables for some of the Entity Tables. The DB about 15 tables as of now (very soon will grow to about 50). For about 20% of these Tables(which are Entities), we're required to back them up (using Triggers) to IDENTICAL copies of Audit Tables. Eg : A Family has 1 or more "Contact"s. A Contact has email, phone, etc info PLUS 1 "Address". So, when a Family gets CREATED or Modified or deleted, using the Trigger, I copy the contents of the Family Table to its AUDIT Table, Family_Audit. Similarly, when a change is made to "Contact" table, I will make a copy of it to the Contact_Audit Table. Same for Address.

Changelog Table : If I already have Audit Tables for each of the Entity Tables that need an "Audit", then what is the point of having a Changelog Table ?

Given this, I wondered if it still made sense for me to use the above "boilerplate" columns.

Any comments ?

More important, what are the boilerplate columns you add to (almost) every Table ? why ?

Answer 1

I try to avoid "boilerplate columns".

If you want a change log, create a ChangeLog table with the user name, timestamp, table name, and table row ID in the log, not on the table.

The only thing that comes close to "boilerplate" is the surrogate Primary Key (called ID).

In most cases, the "boilerplate" -- change history -- isn't even an issue because I try to create designs where history is preserved. I try to reduce the incidence of UPDATE to the fewest possible.

Data can easily be kept -- in it's entirety -- with a "current" record and all previous versions of the record. Disk is cheap nowadays. A historical log of user updates seems to work out better. Reversing a user change is trivial, since the previous versions are all available.

I no longer see any value in "boilerplate columns"

---

how does storing the "table row ID" in the log help if the actual content(in other columns) are NOT saved ?

What? The previous value of the row can be saved. That's the point. You have a variety of methods for retaining history.

1. Separate history table with the previous values.

2. A "flag" -- which creates a two-part key -- with "current" vs. "history" setting.

3. Possibly using an "active on" and "inactive on" pair of dates.

There are other techniques, also. Read about Slowly Changing Dimension (SCD) algorithms.

Each of these techniques has unique requirements; they're design patterns, not boilerplate.