That is a conventional rule that all developers must apply to were I work. I remember thinking it was worthless when I began working there but as time has progressed I have seen it to be very useful for the reasons you stated. Although you might not think so that convention makes complex joins and queries in general easier to comprehend. However, as Dan stated its a personal preference.
i have seen thousands of very complex joins and queries and i still don’t think it’s a good idea to embed the entity name into the column name
speaking of camels, the fable of the camel’s nose in the tent comes to mind…
if you prefer user_id instead of id for the users table, then shirley you will also prefer this –
CREATE TABLE users
( user_id INTEGER NOT NULL PRIMARY KEY
, user_name VARCHAR(16) NOT NULL
, user_pswd VARCHAR(16) NOT NULL
, user_fullname VARCHAR(99) NOT NULL
, user_email VARCHAR(99) NOT NULL
);
to this –
CREATE TABLE users
( id INTEGER NOT NULL PRIMARY KEY
, name VARCHAR(16) NOT NULL
, pswd VARCHAR(16) NOT NULL
, fullname VARCHAR(99) NOT NULL
, email VARCHAR(99) NOT NULL
);
where do you draw the line? and if you say okay, draw the line at “id” then my question is why stop there?
I tend to use user_id or uid or the user id. It helps eliminate confusion in my mind as to which id it is. The rest of the fields…I just make reasonably descriptive so they make sense at a glance. That’s probably the most important thing.
After all, I don’t think most folks would use something like ZipBingWhee_id as the user id. Unless that one makes sense to you…
If you are going to call the foreign key in the other table user_id, then you’re going to have to specify the table name or alias in the join either way, so you add verbosity without much benefit.
“ON user_id = user_id” is going to be ambiguous, “ON id = user_id” will be too if both tables have an id, so you’ll end up with “ON users.id = posts.user_id” regardless of the choice you made… so the longer name didn’t help.
If I need an artificial identifier, I use “id”. I switched from camel case (userId) to underscore (user_id) some years ago for foreign keys, mainly because it’s easier to read quickly, and a good convention if you’re ever going to use an ORM. Many of them default to underscores as the column names become machine parseable (split at underscore, left side is the type of object the FK references).
on the contrary, it is vital (in my opinion) that in any query of more than one table (i.e. a join) that every column used in the query be qualified with its table name (or table alias)
this, to me, is not a matter of preference, it is mandatory, and essential to understanding the query