Readable SQL aliases

Question

Since I'm sure many people have different standard, I've made this post a community wiki.

My question is, what's a good naming scheme for table aliases? I've been using the first letter of every word from the table name, but it's been getting quite unreadable. Here's a quick example.

FROM incidents i
FROM cause_attack ca
FROM obscure_table ot

Thank you.

Answer 1

The whole point of an alias is to shorten the name so you don't need verbosity.

It only needs to be unique within a given query, so there's no need for a scheme for naming them.

Edit: Also, the aliases you'd use depend highly on the table naming scheme. If all your tables have a 5-part name where the first 4 are common across a query, it's silly to keep those parts in the aliases.

Answer 2

The tables names themselves should already be readable. Therefore, if you want a readable name just don't alias.

This means the purpose of an alias is as much to save your poor fingers from re-typing long names as anything else. In that case, short terse names work well, especially as they must be declared right next to the full name.

The only exceptions here is if you're joining a table in more than once, in which case you'll need something to identify which instance of the table you need, or if you're aliasing a sub query.

Answer 3

Usually I try to follow the naming structure of the tables.

I try to use speaking table names, like 'RelObjectProperty', and alias them consistently like (for this example) 'rop':

SELECT
  p.Name    PropertyName,
  o.Name    ObjectName,
  rop.Value PropertyValue
FROM
  Property p
  INNER JOIN RelObjectProperty rop ON rop.PropertyId = p.Id
  INNER JOIN Object              o ON rop.ObjectId   = o.Id
WHERE
  o.Id = 10

This acronym scheme is helpful for a database with strict an collision-free table names, but that cannot always be guaranteed.

There might be a table 'RelObjectPresentation', in which case I would most likely break the scheme and use 'rop' for the first and 'ropr' for the latter. Even in this case I would be consistent in being inconsistent and at least use the 'ropr' alias everywhere, and not just in queries where I need a distinction from 'rop'.

Answer 4

I generally do similar as you do, except I only use the first letter, in uppercase, until I have multiple tables that start with the same name, or multiple refs to the same table, then I add a suffix to distinquish the two... Anything to make it clear to the reader. If I use the same table in a subquery (say Employee table) as is in the outer query, I may use a prefix i or o to distinquish, as in

-- Find Highest paid Emplyees in Each Division ..... 
Select * From Employee oE -- For outer Employee table
Where Salary = (Select Max(Salary) 
                From Employee iE
                Where DivisionId = oE.DivisionId)

This way, when I read the SQL, I can internally read the aliases as "Inner Employee" or "Outer Employee"

Answer 5

In a datawarehousing scenario, I typically use the first characters, but prefix either fact_, dim_, or cdim_ in order to distinguish fact, dimension, or conformed dimension tables. I also will do lkup_ for lookups (so LOOKUP_TRANSACTION_TYPE will become lkup_TT).

The lookup technique would work in OLTP-type databases, too.

Typically, I don't have a huge number of tables in queries where the abbreviations would be hard to follow, and usually there is not any conflict between table aliases (because there is often already some grouping like SYSTEM_SUBSYSTEM_ENTITY_TYPE), so, in effect, the table name always has the same alias.

This is a good advantage over the A, B, C or T1, T2, T3 technique, because it is followable and helps avoid cut-and-paste errors.

Answer 6

Although I'm not an Oracle guy (actually, this question should apply to almost any RDBMS), my answer to "What was the strangest coding standard rule that you were forced to follow" seems to apply well here (edited to make sense within the context of this post) ...

For us, it's all about the table name. We got this idea from a client we worked at that used this standard, and after we all adapted to it, we loved it. The table names are fairly verbose, but due to the unique mnemonic prefix on all of them, we always had a standardized set of aliases: Just use the prefix. Once we disengaged from this client, we kept the naming scheme for new systems, and it's been highly successful ever since.

Here's the scheme: Every table is named in all caps, with underscores between the words. Every table has a prefix (generally 1 - 6 characters) which was usually an acronym or an abbreviation of the main table name. Every field of the table was prefixed with the same prefix as well. The prefixes are also used, in complex queries, as the aliases. So, let's say you have a simple schema where people can own cats or dogs. It'd look like this:

PER_PERSON
    PER_ID
    PER_NameFirst
    PER_NameLast
    ...
CAT_CAT
    CAT_ID
    CAT_Name
    CAT_Breed
    ...
DOG_DOG
    DOG_ID
    DOG_Name
    DOG_Breed
    ...
PERCD_PERSON_CAT_DOG (for the join data)
    PERCD_ID
    PERCD_PER_ID
    PERCD_CAT_ID
    PERCD_DOG_ID

Again, the prefixes are there to be reminders of "recommended" (and enforced!) table aliases when building joins. The prefixing made the majority of join queries easier to write, as it was very rare that you'd have to explicitly reference a table before the field, as even related field names are prefixed and therefore already somewhat name-scoped.

A neat side effect is that, eventually, your developers may be able to begin referring to tables in conversation by nothing more than the prefix. An acquired taste, to be sure ... But it works for us.