Naming conventions - prefixing table and field names?

[hessodreamy]

When I started my current (and first) developer job I inherited a database table naming convention which used prefix of 't' on every table eg tOrders, tProducts etc. Is there any point in this? The only use I can see for this is to differentiate tables from views, but is there even any point in this?

I was reminded of this today, when, as part of an interview process, a candidate demonstrated some work which used 'tbl_' at the start of every table name. Not only this, but he prefixed each field name with 'fld_'. I really can't see the point in this, but I'm presuming he picked the habit up somewhere.

On the topic of prefixes, I do find myself using field names like productID, productName etc. Is it more fitting/clear/easy to just use 'id', 'name' as field names, as id and name are attributes of (for example) a product? So a product's name or id can easily be referenced by product.name, product.id, or using a suitable alias. Or would this make queries with multiple joins ultimately more difficult to read?

[Lemon Juice]

Everyone will have their own preferred convention so I can only speak for myself. I think using prefixes like 't' for tables or 'fld_' for fields doesn't add anything of value apart from requiring more typing. I rarely use table prefixes and if I do they denote different components of a system, for example 'shop_', 'inv_' (inventory), 'forum_', 'log_', etc.

I usually don't use 'productName', simply 'name'. But for primary keys I use the table prefix like 'product_id' - just because most often I will use the same name for corresponding foreign keys in other tables and this way I keep the naming consistent. I think this doesn't matter much in sql itself but in an online application I may be using 'product_id' in many different contexts (variable names, array keys, object properties, template variables, xml elements or attributes, form field names, url query strings, etc.) so I find it easier when I have the same index names accross all my code - insted of an 'id' everywhere.

Other conventions I stick to are small caps for all table and fields names, and upper case for functions and stored procedures (just because native mysql functions are upper case).

[r937]

completely agree with all of lemon juice's suggestions, with one slight difference

the primary key, if it's an auto_increment, is called "id"

foreign keys which reference it are called "entity_id"

this is so that when you run across a query which joins multiple tables, with ON conditions such as inventory.product_id = products.id, it is immediately obvious which one is the foreign key

this structural characteristic is not evident if the join condition is inventory.product_id = products.product_id -- you can guess which one is the parent table in the relationship and which one is the child, but you'd have to examine the table designs to be sure

[r937]

btw, regarding the "fld_" prefix, this has been discussed here before --

http://www.sitepoint.com/forums/show...=1#post4553440

i've never actually found anyone in real life using the "fld_" prefix who was able to offer a valid reason for doing so

nor, come to think of it, for using the "tbl_" prefix either

[Lemon Juice]

inventory.product_id = products.id, it is immediately obvious which one is the foreign key
[...]
inventory.product_id = products.product_id -- you can guess which one is the parent table in the relationship and which one is the child, but you'd have to examine the table designs to be sure

Actually, I don't see a difference. If I stick to the same principle everywhere then when I see products.product_id I am sure that this is primary key, just as cats.cat_id, users.user_id, etc. There is no other possibility.

I'd agree that for writing sql using plain 'id' for PK is very slightly more convenient but I use 'product_id', etc. for the other reasons I listed in my post. In an online application plain sql is used rarely compared to other pieces of code where the same names of columns appear as variables and other kinds of entities.

The OP forgot about one more convention: plural vs singular table names - but maybe don't let's start an unnecessary discussion

[hessodreamy]

but maybe don't let's start an unnecessary discussion

Let's.

I limited my OP to those few examples because I thought the general issue may have already been debated to death on the forum. If not then bring it on!

I must confess that, when it comes to id fields and foreign keys, I do find it handy to call them the same thing, so that I can make my join with USING(idfield). Does that make me a bad person?

[r937]

There is no other possibility.

actually, there is, but let's not prolong an unnecessary discussion

i think we're all agreed that the "tbl_" and "fld_" prefixes are useless

as for differentiating between tables and views, i don't see the reason why you'd want to do that -- in fact, it would be counter-productive, because the whole point of views is to define a particular data definition to look like a table

[r937]

Let's.

it's really more of a personal preference

mine is for plural table names

that way, your e-commerce database doesn't have to wrestle with customer, product, and `order` (or "order" or [order], depending on your sql dialect)

also, it is obvious that a table is a collection of row, and most queries are interested in retrieving multiple row

... make my join with USING(idfield). Does that make me a bad person?

nope, you've just never run into the problem with using USING in multiple table joins

avoid USING and you'll never hit this problem

[Lemon Juice]

Let's.

Most often I use singular just because I translate most tables to PHP objects, so that $product object makes sense as a singular because it contains data of one record. For sql itself plural makes more sense as a table is a collection of multiple items. But either way is fine for me, no big deal.

actually, there is, but let's not prolong an unnecessary discussion

I think I know what you mean so I will just say briefly that if I stick to the naming convention consistently there isn't

as for differentiating between tables and views, i don't see the reason why you'd want to do that -- in fact, it would be counter-productive, because the whole point of views is to define a particular data definition to look like a table

Actually, recently when I started using views I had a reverse problem - how to name the table which is NOT a view? I decided to have a product table with all raw data but actually use a view of the table almost everywhere. I assumed in such case it would be logical to name the view 'product'. Then what do I do with the actual product table? I decided to call it 'product_data'. What's your take on this?

[r937]

What's your take on this?

depends entirely on the purpose of the view

sorry, wimpy answer, i know