So right now I’m looking at a table that has at least 3 different columns that control whether the particular row is displayed on the front end. In some cases that’s unavoidable, but it has to be kept in check.

Maybe you can tell me what the difference is between the intent of these columns: status (e.g. pending, active, canceled) and should_display (0 or 1). In addition to that, there’s one part of the code that will ignore a record if one of the FK columns is null but will consider it if it’s non null.

This is madness. I now have to piece together which columns are significant to which consumers of the data. And then I have to figure out the magical combination of values to make the row appear on the front end. This leads me to some quick rules for database flags:

• Limit the number of display flags to as few as possible. I usually use a is_active or display_order column to determine whether the row should be retrieved. There will be cases where the row should be retrieved by one consumer and not another, but there should never be more than one column that does almost the same thing.
• Use descriptive column names. The ones above are too general. is_active tells me exactly what I need to know.
• You can use a nullable timestamp column to do both boolean checks and date-triggered checks. In other words, if the column is null it means the column is still valid. If it’s not null you have to check it against the current timestamp. This saves a duplicated column and is fairly easy to get across.

Be Sociable, Share!

•