Database Object Naming Standards
Part V: Column Base Names
In Part II: Column Names, the notion of Base Names was introduced. Rather than clutter that article up with the current list, I chose to simply segregate these off into their own article.
amt
An amount, regardless of whether it is an integer or floating point quantity. An example would be Transaction.amt. amt, in this case, refers to the quantity of money associated with the transaction.
cd
A very short code that provides direct identifies subtypes of records in the table. Example: Transaction.cd directly identifies the type of transaction (eg. IN for invoice, CR for credit note) It’s important that the codes chosen reflect the nature of what they encode. A, for invoice, would be a bad choice. Once established, a constraint should be placed on the column to ensure only valid codes are used.
dsc
Text that provides a verbose description.
dt
Local date/time. As localization capabilities become more commonplace, and applications become more global in scope, the need to store any date and time in local time will diminish — you will simply convert to local at presentation to the user. This Base Name should see less and less over time.
flg
A value which is true or false (as in flag set or not set). The world probably doesn’t need another word to describe a binary state, but I have always felt that BIT and BOOL are obtuse, particularly for the generation that has never seen assembly language. flg is likely not that much better, but I think most can relate to the notion of a flag being either up or down, so flg it is. Boy, am I going to get letters about this one.
geom
Spatial data used to represent something in three dimensional space. From the PostGIS documentation: “The OpenGIS specification defines two standard ways of expressing spatial objects: the Well-Known Text (WKT) form and the Well-Known Binary (WKB) form. Both WKT and WKB include information about the type of the object and the coordinates which form the object.” Generally speaking this column should be WKB.
id
A unique identifier which is pretty much reserved for the primary key of the table, and if it is present, it will be the first column of the table, even if that breaks the rule of keeping column names in alphabetical sequence.
img
An image which is usually binary data, regardless of source or what it represents. An example would be Document.img. img would contain binary data related to the document. The type of binary data contained in the img column would have to be captured in some other column in the Document table.
lbl
A very short identifier for the records in a column. It’s likely to be unique, and given that it is, this should be enforced with an index. Example: an invoice number such as AB2345.
nm
The name of the thing the table represents. There’s a pretty good chance that the name found in this column will be unique for the entire table, and I usually enforce that constraint using a unique index. Examples: Dog.nm and Bird.nm.
seq
Used to impose a specific sequence on a group of records within the table.
udt
The UTC date and time In the future this will become the standard method of storing date and time.
xml
Well-formed XML data, regardless of source or what it represents.
©2019 Intellog Inc.
This series of articles was originally published by Terence C. Gannon in 2007 in a primordial version of The Intellog Blog. As we undertake a brand new, clean sheet database design for a new client, we decided to go back to these source documents to see if they have stood the test of time. For the most part, they have, but we also took the opportunity to update them a little for a more modern era. We would love to hear your feedback.
