23

We're using a database setup from a vendor's application that has horrifically hard to read database table names, and no documentation on what is stored where. I can see why one might want to obfuscate their table structure in a proprietary app, but one of the selling points of this application (Enterprise Resource Planning) was it's customizability.

Table names are like aptrx (Accounts Payable Transactions) and apmaster_all (curiously, this is the vendors table). It's an extremely complex database, so I was wondering if there was any logic to the convention or if it was simply being obfuscated intentionally or otherwise.

To the best of my knowledge the length of the table name won't affect performance noticeably, correct? The database is very complex (hundreds of tables) so sorting makes sense, but I can't imagine why AccountsPayableTransactions isn't preferable to aptrx....

6
  • 8
    someone hasn't been smaked in the back of the head hard enough to know better
    – DForck42
    Commented Sep 9, 2011 at 20:10
  • 2
    *smirks* it's for job security, the cost of firing old programmers and hiring new ones becomes much higher if you have cryptic names.
    – Lie Ryan
    Commented Sep 9, 2011 at 23:55
  • @Lie_Ryan that certainly seems to be the case, that they'll hope you'll hire a consultant...
    – Zelda
    Commented Sep 10, 2011 at 0:03
  • FWIW, if you work on accounting systems, "aptrx" isn't cryptic. It's obvious. More details in my answer below. Commented Sep 10, 2011 at 12:05
  • obfuscation is one reason Commented Sep 10, 2011 at 12:58

9 Answers 9

24

Oracle has had a long standing limit on table names of 30 characters. I suspect this is a legacy issue based off an original 16 bit environment.
The length of a table name could have some minuscule effect on performance as all the names have to be stored in a data dictionary and also parsed for queries but I don't think you could measure the hit.

A more important effect of short table names is that it's hard to work with. I too have to maintain an enterprise database schema with short names. There is no good reason to have short table names. Ease of maintenance trumps obfuscation or old DOS habits every time.

1
  • 3
    If 30 characters aren't enough to be able to come up with unique names for tables, you have a much more serious problem than any DBMS or development environment can solve : you have a problem with the level of expressiveness of your language and/or vocabulary. Commented Oct 11, 2012 at 14:24
18

I feel there are two things that still need to be said or elaborated:

  1. Naming things is not as trivial as it sounds

    There are only two hard problems in Computer Science: cache invalidation and naming things. Phil Karlton

  2. Whilst short meaningless names are always bad, long names are not always good - our brains have an inbuilt tl;dr threshold which is surprisingly low. 30 chars is usually enough but I prefer the RDBMS to allow more for the exceptional cases when it isn't (and just like in language, longer names are more useful for things we don't speak about so often - like constraint names, and shorter names are more useful for tables we query all the time)

I am always tempted to spend too little time choosing names, and always regret it later if I do - changing names happens only rarely

1
  • 2
    I'm very picky about names and my current limited ability to change them bugs me to no end. I'm into UX though, so the non-usable names might bug me especially much. Plus I just plain prefer camelCase...
    – Zelda
    Commented Sep 10, 2011 at 0:04
8

Laziness. IntelliSense and 3rd party options make typing a real tough excuse to justify. I'd much rather the names have meaningful and readable words.

8

Table names are like aptrx (Accounts Payable Transactions) and apmaster_all (curiously, this is the vendors table). It's an extremely complex database, so I was wondering if there was any logic to the convention or if it was simply being obfuscated intentionally or otherwise.

Well-known abbreviations are usually preferable to spelling things out. When an abbreviation is well-known to some people, but not quite enough people, we stop calling it an abbreviation, and start calling it a code.

Abbreviations conserve space on platforms that have tight limits, although this is less important now than it was 30 years ago. (I seem to recall working on a system in the 1980s that limited you to either 6 or 8 characters for a table name.)

Abbreviations usually make table names and column names easier to read, as long as the abbreviating is done well. If I worked on code for AP all day, I'd rather read column names like "ap_trx.inv_num" than "accounts_payable_transactions.invoice_number". (I like underscores.) Typing long names isn't much of an issue with a good text editor.

In accounting systems, both "ap" and "trx" are well-known abbreviations. Others include "ar", "gl", and "gj", for accounts receivable, general ledger, and general journal.

In a well-designed system, if I found accounts payable transactions in a table named "aptrx", I'd hope to find accounts receivable transactions in artrx, general ledger transactions in gltrx, and so on. I find "apmaster_all" a little puzzling, but if I also found "armaster_all", I'd presume that the first held all the vendors (as opposed to active or inactive vendors), and that the second similarly held all the customers.

In other problem domains, you find other well-known abbreviations. In addressing, you'll find abbreviations like "addr" for address, "st" for street, "usps" for United States Postal Service, "ups" for United Parcel Service, "cty" for county, "zip" for Zone Improvement Code, and so on.

I wouldn't call this obfuscation. If accounts payable transactions were stored in a table named "cdrs21", I'd call that obfuscation. (Although I did once work for a company that named all their mainframe assembler modules that way. Character limits, not obfuscation.)

But useful databases grow, and you run into a problem when databases get big. As you add problem domains to your database, you run into situations where well-known abbreviations collide. If you deal with the media, then "ap" could also abbreviate "Associated Press", "alternative press", or "advance placement". When that happens, it's time to either abandon abbreviations, or switch to codes. The bigger the organization (and the bigger the database), the more frequently I find codes.

3
  • 4
    Part of the problem is that these tables are not being maintained by accountants, they're being maintained by a systems analyst and generally our IT Dept. aptrx is actually one of the most logical names I've found, one of the only ones I've remembered. Also note that there are several hundred tables; the basic abbreviations like "ap" for "accounts payable" are very easy to learn, the literally 100 suffixes after "ap" are not...
    – Zelda
    Commented Sep 10, 2011 at 15:46
  • Will you please provide examples and/or references to how you use codes instead of abbreviations?
    – Arya
    Commented Jan 20, 2022 at 15:50
  • 1
    @Arya: Abbreviations and codes don't differ in how they're used. They differ in how meaningful they are to <insert name of reader here>. Commented Jan 20, 2022 at 19:09
4

Just chiming in with "my god, the goggles they do nothing for this horrible naming convention" story. The data management team at my last environment stated the reason for using abbreviated table names was a DB2 limitation (we had DB2 on z/os and SQL Server) of 18 characters for tables and columns. I promptly pointed out this was inaccurate with documentation from IBM's site. They then stated it was a COBOL issue (yes, they were actively developed COBOL) in case it needed to talk to the database which was then disproved by the MF jockeys. Finally, their response was it's our publish standard.

We petitioned the standards committee to increase the length from 18 to 32 character and received 30 character limitation. That resulted in tables going from useless names of 'SR_M_DLY_ADV_PRD_S' to 'IDX_FDSHRCLAS_LIF_RTRN_STATS_X' FML

So, in my dozen or so years of experience, shortened table names provide no tangible benefits and result in a higher cost of development and maintenance as I must always refer to data dictionaries to translate the garbage on screen to a meaningful identifier. Which can be contrasted with logically named entities I have worked with and can mostly recreate from memory because they were intuitively named.

4
  • 1
    seems like the names goes from totally useless names to slightly less useless names. Perhaps normalization could help? If each table do less, then there is less reasons to have long multiword names, so less reason to abbreviate.
    – Lie Ryan
    Commented Sep 10, 2011 at 0:01
  • Not really, that obnoxiously long table has could not do less if it tried. It has 4 columns in it, 2 of which were foreign keys. It's the "return stats" table to anyone but those protecting the sacred data dictionaries of knowledge. There it's the Index fund share class lifetime return statistics cross reference table.
    – billinkc
    Commented Sep 10, 2011 at 0:15
  • you just blew my mind with that; perhaps I'm just unfamiliar with the problem domain, but the table isn't immediately obvious to me even after seeing the unabbreviated name. A few question in my mind (just a list of things that weren't immediately obvious to me, you don't have to answer them if you don't want to): Is this an entity table or a relationship table? Does "index" have anything to do with "database index"? By "cross-reference" and "return statistic", that seems to hint me that this a denormalized aggregate table (which can be useful when calculating them are expensive)?
    – Lie Ryan
    Commented Sep 10, 2011 at 0:50
  • Financial services industry, entity table, the indexes that rate an investment (mutual fund share class in this case) had statistics about something that I can't remember...
    – billinkc
    Commented Sep 10, 2011 at 2:02
3

It is a habit (I agree with Kevinsky). It was reaction on some old (maybe exist) issues to restriction (name length, space between words of complex names, multilingual etc) of operation system (DOS, Windows, for example) and some software that didn't handle so names. Experienced people said: "Do so (use short and separated with underline names) and all would be ok."

2

I like to use descriptive naming for the aforementioned reasons by posters.

But there is also another benefit. For instance, with descriptive naming, it allows you to use nested names. Say you have a table called Employee. If you have a relationship to another table, it could be called EmployeeAddress. Or EmployeeDepartment. With the cryptic, abrreviated naming this is almost impossible.

0

Depends on how complex the underlying definitions of each column are. I think people get lazy with metadata management when they see these kind of very descriptive column names, and they even are in fact incomplete descriptions. You might as well ask why abbreviate anything.

1
  • Since the tables don't provide any non-automatic metadata I'm not sure that's a valid argument...
    – Zelda
    Commented Sep 10, 2011 at 15:48
0

It/s Real Estate. Every BIT you save in width, correlates to a faster scans and less disk space.

Rule of thumb on column names is to take out the vowels.

fstnm, lstnm

If you save 1MS on everything you do, yay!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.