There are a couple of columns that I always include on any tables that contains that that will be displayed to front end users. Over time I’ve developed a preference that these be declared as CHAR(1) with a check constraint to limit them to y and n (or Enums in MySQL). The columns are:

• is_active
• display_order

The is_active column is used to determine whether that row should be included in the result set.

SELECT u.name
FROM users u
WHERE u.employer = ‘R/GA’
AND u.is_active = ‘y’

This is cheap way to support a “safe” delete. I’m very wary of ever removing non-corrupted data from a database. Besides the fact that it’s the database’s job to keep data, it’s a dangerous thing. All it takes is a simple bug in the routine that deletes the rows and you get to spend all afternoon with a DBA recovering data from tape. Or worse yet explaining to an end user why they have to re-enter their data. It’s usually not worth the “purity” of the data. The is_active column helps avoid that mess, and keeps the data around so that it can be used later for reporting, etc. Think about it – you have a business owner breathing down your neck because there’s some invalid data showing up on the front end. Which would you rather do? Update a column from “y” to “n” knowing you can change it back if you made a mistake, or delete the rows permanently and hope you didn’t mess up?

The display_order column is a acknowledgement that everyone will have an opinion on how data should be displayed on the front end and the opinion always changes as you go up the corporate ladder.

SELECT c.color as featured_colors
FROM couches c
WHERE c.name = ‘lazy-boy-recliner’
ORDER BY c.display_order

It’s never a good idea to rely on row IDs unless you truly have to. So by having a built in display_order column we allow the business to change their mind as frequently as they want with minimal impact to our pretty code.

Both these columns embody my personal philosophy of letting the database do as much of the work as possible. It takes a while to learn to use the database as more than just a data store – to learn that it can have its own inherent, hidden logic as much as your client code can. In the end it leads to more robust data models that can stand up to changing requirements and emergencies.

Be Sociable, Share!

•