Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
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

3 Answers 3

up vote 8 down vote accepted

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

share|improve this answer
    
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 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

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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