Image for post
Image for post
And you thought your promotion to the Computing Section of the Computing Division was a good thing? (image credit: Silodrome)

Database Object Naming Standards

Part V: Column Base Names

Intellog Inc.
Aug 19, 2019 · 3 min read

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

This series of articles was originally published by 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.

The Intellog Blog

Digital Content | Social Media | Digital Project…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store