21

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!

Derek Downey
  • 23,568
  • 11
  • 79
  • 104
Kerrek SB
  • 313
  • 2
  • 6

3 Answers3

14

This has been asked before on programmers.stackexchange.com.

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

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
gbn
  • 70,237
  • 8
  • 167
  • 244
7

In the book SQL Antipatterns: Avoiding the Pitfalls of Database Programming, using id is not recommended, and I would agree with the author.

This is a particular problem when you are doing complex reporting and need more than one id, as you then have to alias those columns. Using <tablename>_id makes it easier to identify the correct foreign key to join to (as they have the same name) and makes errors from joining to the wrong column less likely.

That said, many databases don't support the USING syntax, which makes the problem a non-issue for those databases. Nor do many databases support using a natural join which I would also not recommend using, as the join could change if the table structures change. For example, you might a column called modified_date to both tables, you would not want to join on those columns but the natural join would attempts do so.

Iain J. Reid
  • 103
  • 2
HLGEM
  • 3,153
  • 18
  • 18
5

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

Eralper
  • 182
  • 1
  • 6