In the case of Oracle, you'll want to not name columns 'id' or 'name' or anything generic.
The issue is that by default in older versions, Oracle will attempt to join tables based on similar column names, so if I've named everything well, then I've also ended up specifying the default join clause between my tables.
But even if you're not using Oracle, by not chosing names that appear in multiple tables, it also means that you don't then have to go through the trouble of aliasing every time you have to do a select across two tables:
instrument.name as instrument_name,
instrument.abbr as instrument_abbr,
source.name as source_name,
source.abbr as source_abbr,
So, if multi-table selects are the norm, longer column names save you typing. (if you're only using one table at a time ... do you really even need a relational database?)
... and saving typing brings us to another issue in Oracle -- at least in 8i (the current version when I took the Oracle SQL Tuning and Data Modeling courses) caching of execution plans is based on only the first so many characters of the query (can't remember the exact value ... 1024?), so if you have queries that only varies by something all the way at the end of the where clause, and a really long list of columns you're extracting, you can run into a performance hit as it can't cache the execution plan correctly.
Oracle had a guide on selecting what they claim are good table and column names, which is basically a guide for removing letters 'til it's about 5-8 characters, but I never much cared for it.
As things go other than that:
- columns are always singular (tables are always plural)
- all names are lower case, just in case there's something case-sensitive
- as a result of the above, use underscores instead of camel case.
update : for those not familiar with Oracle's join behavior, see the last example on Mastering Oracle SQL : Join Conditions, where it mentions:
What happened? The reason lies in the fact that, aside from supplier_id, these two tables have another pair of columns with a common name. That column is name. So, when you ask for a natural join between the supplier and the part tables, the join takes place not only by equating the supplier_id column of the two tables, but the name column from the two tables is equated as well. Since, no supplier name is the same as a part name from that same supplier, no rows are returned by the query.
Under 'old join syntax' (8i and earlier), 'NATURAL JOIN' was the default join behavior, and I believe it still is if you don't specify a join condition. Once 'NATURAL JOIN' was an official option in 9i, the general recommendation was don't use it, because bad column naming can screw you up, which is my I'm advocating for good column names.