The SQL Holy Trinity

Like most web-based application developers, most of my work consists of inserting/extracting records to and from a RDBMS, then generating output based on the results of a SQL query. I have learned that, whether they are required in the initial specification or not, there are three columns that every table should contain.

Unique ID

Being able to uniquely identify any record is absolutely vital if your program is to be efficient. Stepping through a 10-record table with a "while" loop is okay, stepping through a 10,000 record table is worthless and terribly inefficient.

"But my 'employees' table will never exceed 100 records!"

Trust me on this, never take anything for granted. Plan for the worst possible case, then multiply the result a thousand-fold - you'll still probably be wrong.

Make the first field of every table:

"id" int(8) unsigned NOT NULL auto_increment

Last Modified

You might not need to know right now but, at some future time, you're probably going to want to be able to establish the date and time of the last revision of a record.

Make the second field of every table:

"last_modified" timestamp(14) NOT NULL

Creation Timestamp

Again, it might not be important to you today but set a creation timestamp for every record and make it immutable. Make absolutely certain that no future "UPDATE" touches that field, ever - under any circumstances.

Make the third field of every table:

"created" timestamp(14) NOT NULL

Apply these three golden rules to every database and you will be protected from a world of pain. I guarantee it!