What are the best practices for designing DB Tables

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 se开发者_C百科nse for me to use the above "boilerplate" columns.

Any comments ?

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

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.

You're correct that these columns are logically redundant in the presence of a full audit table.

The advantage of keeping any of them would arise if you had a need for simple, index-based access to queries such as:

• List the records created in a particular date range (although this could still be answered from the audit table fairly easily).
• List the records last updated by a particular person (less easy to optimise such a query).
• Show the most recently updated 100 records (again, not as easy to optimise on the audit table).

I would leave them off, and add them back in only if a need arises. You can populate values for existing rows from the audit table at that time.