Best practices for using mysql field names.

[PHPycho]

Best practices for using mysql field names.

CASES:

1>Type1

---------
users
---------
id
first_name
last_name
email
password
----------

VS

2> Type2

---------
users
---------
user_id
user_first_name
user_last_name
user_email
user_password
----------

ie prefixed with singular table name : user


I would like to know the opinions of forumians that which naming conventions (among two as shown above)
do you deploy and why?

Thanks

[r937]

never, ever option 2

embedding the table name into the column name is unnecessary, wasteful, and above all, obfuscating

it makes queries ~so~ much harder to read

[PHPycho]

never, ever option 2

embedding the table name into the column name is unnecessary, wasteful, and above all, obfuscating

it makes queries ~so~ much harder to read

I also prefer the #1 usage. But in some open source like dotproject i got such table name prefix in table fields, so thought to arise the post in sitepoint.

Anyway thanks Rudy for the opinion.

[jspash]

So what do you do in the situation where you have two tables such as 'product' and 'category' and they both have a column named 'description'. If you do a simple * and join the tables, the resulting set of data will have a duplicate field.

select * from product p, category c where p.id=c.id;

result->description = ???

Yes you could do this:

select p.description as `product.description`,
c.description as `category.description` etc..

.

But that means any time you change the database, you have to update all your data access code.

Is it recommended to alias each column on every query as I've done in the 2nd query above?

[r937]

Is it recommended to alias each column on every query as I've done in the 2nd query above?

when necessary, yes