Database Naming Conventions – Should You Use Abbreviated Table Names?

naming convention

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….

Best Answer

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.

Related Solutions

Why do people recommend not using the name “Id” for an identity column

The table name prefix has very good reasons.

Consider:

TableA (id int identity, stringdata varchar(max))

TableB (id int identity, stringdata varchar(max))

We want to DELETE from TableA records that exist in both tables. Easy enough, we will just do an INNER JOIN:

DELETE a
FROM 
  TableA A
INNER JOIN 
  TableB B
    ON b.id = B.id

....and we just wiped out all of TableA. We inadvertently compared B's ID to itself - every record matched, and every record got deleted.

If the fields had been named TableAId and TableBId this would be impossible (Invalid field name TableAid in TableB).

Personally I have no issue with using the name id in a table, but it's really a better practice to preface it with the table name (or entity name, if TableA were people then PeopleId would work fine too) to avoid accidentally comparing to the wrong field and blowing something up.

This also makes it very obvious where fields come from in long queries with lots of JOINs.

MySQL case sensitivity for table names

The SQL standard defines that identifiers are not case sensitive (unless double-quoted: "CamelCase"). Your desire for CamelCase (mixed-case) names conflicts with the standard and you'd better learn to live with it. Or you start double-quoting your identifiers and never forget to do so.

Most RDBMS actually implement standard(-like) behavior. Even MySQL tries, lately, but it's very hard to break with established syntax rules.

Resources

MySQL manual on this topic.
Related article running a test case against the most popular RDBMS.
A list of how the most prevalent SQL dialects deal with delimited identifiers in wikibooks.

Best Answer

Related Solutions

Why do people recommend not using the name “Id” for an identity column

MySQL case sensitivity for table names

Resources

Related Question