19

I would like some expert opinion on best practices when it comes to column naming.

The background is that according to Wikipedia, the following syntax,

SELECT ... FROM Employees JOIN Timesheets USING (EmployeeID);

is more efficient than

SELECT ... FROM Employees JOIN Timesheets ON (Employees.EmployeeID = Timesheets.EmployeeID);

However, the JOIN ... USING syntax only works of all primary key columns have globally unique names. Thus I wonder if this is considered The Right Thing to do.

Personally, I always used to create tables with PK column id, and foreign key column othertable_id. But that way it's not possible to use USING or NATURAL JOIN.

Any links to design styles or best practice guides for table design would be appreciated, too!

  • 3
    Wikipedia is wrong. The first version is by no means more efficient than the second. Under the hood the database will create absolut identical queries – a_horse_with_no_name Jul 7 '11 at 12:05
13

This has been asked before on SO.

Where you have common and very ambiguous names, then prefix with table name. That is, anything you're liable to have to alias in almost every query.

So for an Employee table I'd have

EmployeeID
EmployeeName
Comment
Salary
StartDate
EndDate
InsertedDateTime
...

And Wikipedia actually says:

The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join.

That is one less column. You'd never use SELECT * anyway so the point is moot...

  • I hadn't thought of searching SO - silly me. Do you have links to any particularly good SO questions? Anyway, thanks, I shall stick to the "unique ID" naming henceforth! – Kerrek SB Jul 7 '11 at 9:35
  • @Kerrk SB: actually, I haven't. I tend to ignore them because each one gets answered or closed quickly :-) Sorry – gbn Jul 7 '11 at 9:40
  • I would like to see those similar questions on SO, if anyone can dig up a link. I came here because I couldn't find a question there, and I was sure it'd have been asked already. I immediately found this question though. – Nicholas Shanks Jul 4 '13 at 7:40
  • It was on programmers.SE. A nice big bun fight for you... programmers.stackexchange.com/questions/114728/… – gbn Jul 4 '13 at 8:08
6

THe following book talks about using ID as a SQL antipattern and I gree with the author that it is. http://www.amazon.com/SQL-Antipatterns-Programming-Pragmatic-Programmers/dp/1934356557/ref=sr_1_1?s=books&ie=UTF8&qid=1330025134&sr=1-1

This is a particular problem when you are doing complex reporting and need more than one id as you then have to alias. Using tablename ID also makes it easier to identify the correct FK to join to (as they have the same name) and makes errors from joining to the wrong thing less likely.

That said, many databases don't support the USING syntax which makes the problem you brought up not an issue for these databases.Nor do many databases support using a natural join which I would not recommend using in any even whatsoever as they join could change if the table structures change. So suppose you add a field called modifieddate to both tables, you would not want to join on that but the natural join would do so.

4

It is better to explicitly give table name and column name like Employees.EmployeeID with expressions where a join exists

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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