Database Object Naming Conventions
Yesterday I tossed out the question regarding the relevance of naming conventions for hardware and software services. Today I’d like to share some insights others have shared regarding naming conventions for database objects as well.
Jim writes:
I think the answer lies with a couple of facets to “it depends”.
It first depends on how big the enterprise is. Let’s say you are customer of a big credit card issuer and new database elements are being created daily at this company. You become considerably annoyed that you are not getting your mail after changing your mailing address with their customer service department six months ago. You see, there is no central administration of metadata and disparate applications are being created at this card issuer. The customer service department cannot know all the platforms where your particular address is located. It is a classic case of the left hand not knowing what the right hand is doing.
The company might have the customer’s address defined as CustAddr, CustomerAddress, CusAd, CA, CAddress, etc. (you get my point) and there is uncertainty about which ones are truly supposed to change. Granted, metadata management is not exactly the same as naming conventions — and a tight management of metadata can compensate for a lack of naming consistency. But it makes the job harder – and this is a silly example. The rule of thumb here is this: the bigger the enterprise, the more important data quality becomes. If the company is a global enterprise, the matter is even more imperative (due to differences in customs and language).
The second “it depends” deals with how much cross marketing the enterprise will be engaged in. For many companies, this is vitally important. But the challenge will be know what data elements are truly the same. For instance, Accounts Receivable calls one data element “Fred”, but marketing calls the same thing “Martha”. Or maybe Accounts Receivable calls a data element “Fred” and marketing calls something different “Fred” as well. Can you see the confusion just in one department talking to another department when they are in the same company?
If your company size is 50 people, then forget what I just said. But even still, I would keep handy a data dictionary – even if it is simply a spread sheet.
I received this question from a developer wishing to remain anonymous regarding the names of objects/columns, etc. in an SQL server database…
Fifteen years ago naming conventions seemed useful since DB2 table and column name lengths were very limited. Today they seem pointless and painful. Below is an example of a production table created long after those length restrictions were eliminated. Do you see others still using such cryptic names?
CREATE TABLE GSA344.HSIT34_ACD_RPT (
ACCD_KEY INTEGER NOT NULL WITH DEFAULT,
LCL_REF_PSN_I CHAR(15) NOT NULL WITH DEFAULT,
AGT_REF_ACD_I CHAR(20) NOT NULL WITH DEFAULT,
HIT_RUN_S CHAR(1) NOT NULL WITH DEFAULT
PPP_CDE CHAR(1) NOT NULL WITH DEFAULT
)
Personally, I think the better name you use in your database objects, the higher value the quality of the data will be. I find the data quality goes up higher if you don’t use the same name to mean different things. More on that topic tomorrow.
Send a note to btaylor@sswug.org if you have thoughts you would like to share.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Office 365 SharePoint Online – Architectural Considerations
Written by AvePoint
The goal of this white paper is to clarify strategies f… (read more)
Featured Script
admin script – move log files to a different drive
Can’t believe how many times I’ve gotten to use this script to generate the detach, copy and attach staements to move log fil… (read more)