7 Reasons Why Tables and Columns Have Confusing Names

If you work with databases, there's a good chance that you haven't designed them. I am guessing that quite often you are struggling to understand what does a specific table or column holds. And what was the person naming them thinking.

1. Little Understanding of the Domain

One of the reasons why names are misleading is that the developer had little understanding of the domain. Real business analysts and architects (I hope) have a strong drive to understand the business domain, the problem and model it properly. Developers, or coders, on the other hand, are more focused on classes, functions, forms, tables, variables etc., all the, you know, real stuff.

"Business unit, Division, Department? No such thing. But, there's a dept table."

2. Complex Domain

Sometimes, the domain itself is very complicated. I have worked for many industries (banks, shipyards, gas companies and even tour operators), but there was a time when I was hired to build reporting system on the network infrastructure for the large telecommunication company and after spending a year+ on the analysis I still had a very vague understanding of all the devices, connections, ports, etc. Let's better not bring that memories back. Sometimes you don't even know what you are looking at even if someone points you to it.

3. Overcomplicated Design

Sometimes, the design is overcomplicated and, for instance, tries to put too many data into one table. Yeah, let's create two tables - documents and dictionaries, both with type column and put all the data there. Data that should be split into tens of tables. I've seen that...

4. Changing Requirements

It often happens, especially in changing business environment or agile projects, that tables and columns change their purpose over time. What started as a customer_email might now hold also letters, calls, or some other form of contact and should probably be named customer_contacts or customer_interactions. But in databases it's really hard to rename something. There are probably numerous references (applications, scripts, reports etc.) to this table/column outside of the database so you can't really tell what that is or how many. So it stays named customer_email for all the future data analysts, developers, DBAs to try and make a sense of it all.

5. Packaged Applications

Slightly similar to the previous one is the use of packaged applications (like CRM or ERP). You start with the predefined data model that is customized just to little extent. Key tables and columns keep their names. And they were designed for something too broad or different than what you will use them for that they are misleading at the very beginning.

Oracle application has a table called mtl_system_items_b and it holds inventory items that represent raw materials and finished goods. The thing is, the company I worked for used it only for raw materials and they were always referred as "materials". Finding this table wasn't obvious.

6. Lazy Architect

Sometimes (often?) architect just is lazy and doesn't want to put any effort in meaningful and useful naming and ends up with the first thing that sounds about right. Proj for projects, why not? Keep it consistent throughout the database? Why bother...

7. Architect Wanted to Save Space

In the old days, when disk and RAM space was precious, each byte mattered and designers were forced to pick short, cryptic names. Take SAP for instance, one of the most popular ERP application in the world. Its table names have names like AFPO (order items), BKPF (accounting documents) or KNA1 (customers). Nice.

So What?

So why is that a problem? Well, if you can't tell what a particular column holds, or where to find it, you waste a lot of time on the discovery, or worse, build on top on wrong assumptions. Build something (a report) that instead of making you wiser, misleads you.

So let's make a world a little better and put a little time into naming the tables and views we create. Or at lease create aliases in the documentation with a tool like Dataedo.