Database naming conventions

[eluser]pashj[/eluser]
I'm no expert, by any means. But I prefer prepending the table name to the columns as the original poster suggested. The reason being duplicate column names in different tables. EX.

Code:

PRODUCT TABLE
product.id
product.title
product.description
etc...

CATEGORY TABLE
category.id
category.title
category.description
etc...

QUERY
select * from product p join category c on p.id=c.id;

The result for 'title' and 'description' come from the product table. You could reverse the join, then the result for duplicate columns would come from the category table. (To be fair, both columns are retrieved, but only one can be accessed via the name. You can get both by using an index.)

OK, so what do I do if I wanted both titles and descriptions? Yes, aliases would do the trick:

Code:

select
p.title as `product_title`,
p.description as `product_description`,
c.title as `category_title`,
c.description as `category_description`
from
product p join category c on p.id=c.id;

But this means every query needs to be carefully constructed (time, effort, possibility for bugs). It seems like the long way around. If the column names were already prepended with the table name

Code:

product.product_id
product.product_title
product.product_description
etc...

Then the result set would be unique with no extra work. I can continue to use select * queries. I can add/remove columns without having to dig through the code and make changes.

It just works. It's verbose, but it works. IMHO