Reading SQL Aliases
Since I'm sure a lot of people have different standards, I made this community wiki post.
My question is, what's a good naming scheme for table aliases? I use the first letter of every word from the table name, but it becomes completely unreadable. Here's a quick example.
FROM incidents i
FROM cause_attack ca
FROM obscure_table ot
Thank.
source to share
The whole point of a nickname is to shorten the name so you don't need verbosity.
It must be unique within a given request, so there is no need for a schema to name them.
Edit: Also, the aliases you use are highly dependent on the table naming scheme. If all your tables have a 5-pair name, where the first 4 are common to the query, it is silly to keep those parts in aliases.
source to share
The names of the tables themselves should already be readable. So if you want the readable name just not to be an alias.
This means that the purpose of the alias is to save your poor fingers from retyping long names like anything else. In this case, short short names work well, especially since they must be declared right next to the full name.
The only exceptions here are if you are joining a table more than once, in which case you need something to determine which instance of the table you want, or if you impose an additional query.
source to share
I usually try to follow the table naming structure.
I'm trying to use talking table names for example 'RelObjectProperty'
and their aliases are the same (in 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 abbreviation scheme is useful for a database with strong table names without conflicts, but this cannot always be guaranteed.
Maybe a table 'RelObjectPresentation'
, in which case I will most likely break the schema and use 'rop'
for the former and 'ropr'
for the latter. Even so, I would be consistent and at least use the alias 'ropr'
everywhere, not just in queries where I need to distinguish from 'rop'
.
source to share
I generally do the same thing as you, except that I only use the first uppercase letter until I have multiple tables starting with the same name, or multiple references to the same table. then I'll add a suffix to distinguish the two ... Anything to make the reader understand. If I use the same table in a subquery (like the Employee table) as in the outer query, I can use the i or o prefix to delimit, 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 SQL, I can internally read aliases as "Internal Employee" or "External Employee"
source to share
In the scenario datawarehousing I usually use the first few characters, but: prefix fact_
, dim_
or cdim_
to distinguish between fact tables, dimension or the appropriate dimensions. I'll also do a lkup_
search (so it LOOKUP_TRANSACTION_TYPE
will lkup_TT
).
The search method will also work on OLTP databases.
As a rule, I don't have a lot of tables in queries where abbreviations will be difficult to follow, and usually there are no conflicts between table aliases (because often there is already some type grouping already SYSTEM_SUBSYSTEM_ENTITY_TYPE
), so, in fact, the table name always has the same the same pseudonym.
This is a good advantage over the A, B, C or T1, T2, T3 technique as it is consistent and helps to avoid cut and paste errors.
source to share
While I'm not an Oracle guy (actually this question should apply to pretty much any DBMS), my answer to the question "What was a weird standard coding rule that you were forced to follow" seems to apply well here (edited to make sense in the context of this post) ...
For us, it's all about the table name. We got this idea from a client we worked with using this standard, and after we all adapted to it, we loved it. The table names are quite verbose, but because of the unique mnemonic prefix, we have always had a standard set of aliases on all of them: just use the prefix. Once we disconnected from this client, we kept the naming scheme for new systems, and it has been very successful since then.
Here's the outline: Each table is named in all headings, with underscores between words. Each table has a prefix (usually 1-6 characters), which is usually an abbreviation or abbreviation for the main table name. Each field in the table was prefixed with the same prefix. Prefixes are also used as aliases in complex queries. So, let's say you have a simple scheme where people can own cats or dogs. It will 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 should be reminders of the "recommended" (and forced!) Table aliases when creating joins. The prefix simplified most join queries, as it was very rarely necessary to explicitly reference the table before the field, since even the associated field names are prefixed and therefore already somewhat named.
An acute side effect is that your developers may end up referring to tables in conversation by no more than a prefix. Of course, acquired taste ... But it works for us.
source to share