Best practice for naming SQL table columns

Question

I will get involved in a big project and I would like to know which is, in your opinion, the best practice for naming SQL table columns.

Lets say we have a products table. Which of the following naming will you prefer?

• id,
• name,
• description,
• photo,
• price

or

• product_id,
• product_name,
• product_description,
• product_photo,
• product_price

Answer 1

Definitely the first one. Second is a violation of DRY principle.

Answer 2

Personally, I find the product_ prefix redundant and irritating. This is, however, ultimately, a matter of personal preference (or team consensus).

Answer 3

Prefixing column names with the table name does not add any benefit and clutters up code. The first choice is better. Only column "ID" may have benefit with the prefix as this is likely a key, and there are likely other tables with that same column.

Answer 4

The first one, without a question. Naming the id column Product_id might be useful in some cases (though I can't think of any at the moment), but otherwise there's absolutely no point in repeating the name of the table in the name of each column. And in case you choose the other alternative, I've grown to dislike _ in column names, so I'd use ProductId instead.

Answer 5

I would go with the first choice, don't repeat the table name in the field, what happen if the table change name?

if you are going to use a lot of table with same field name, it doesn't matter, use aliases

Answer 6

If I have a products table and a customers table my id becomes ProductID and CustomerID respectively. I find it simplier since ID could mean anything especially if you are trying to return both in a query. But it is all a matter of preference.

But keep it consistant is all I can say.

Answer 7

I'd think in most cases the first would be fine, since you generally need to specify the table in your queries. So if the name of the table (or alias) is descriptive, then things like product.id would be clearer than product.product_id.

Answer 8

I'd add the table name only for the ID eg. product_ID merely due to personal preference and leave the rest as they are on your first example.

Answer 9

The first option is my choice simply because one already knows what table the fields are from. In addition, I'll use <table name>_<key field> in other tables as a foreign key field name (product_id, for example).

Answer 10

I would only prefix the ID (PK) column with the table name as you can have consistent names for columns when using it as a FK in another table. No need to prefix the other columns.

Answer 11

I Think this is very important.

While defining the own table ID you should use only ID, however when defining a foreign key field you should use the table name.

A table with a name Table1 and the filds:

• Table1Id
• Table1Name

This doesn't make sense, right?

However On foreign keys the foreign key field should have its table name prefixed.

Answer 12

I agree with those who say that the table name should be omitted from all the columns except for the ID.

If a column is a foreign key, then I like to give it the same name as the column it references. This tends to keep things simpler and easier to remember. There are two exceptions to this rule. If more than one foreign key references the same column in the same table, then they will need some kind of prefix based on their use to distinguish them. And if a foreign key has a specialized use then it may need another name (ManagerPersonId rather than PersonId for example).

This rule about foreign key names encourages prefixing the ID column with the table name.

Also, if a table's primary key is a synthetic identifier, like an identity column, then it becomes very natural to name it TableNameId, since that describes exactly what it is.

In all other cases, prefixing column names with the table name is needless, redundant, and verbose.

Answer 13

Tables

Product
ProductCategory - prefix with underlying table name, could be other categegories

Columns

ProductID,      - prefix with a table name
CategoryID,     - note that it's not a ProductCategoryID
Name,
Description,
Photo,
Price

Keys

PK_Product_ProductID  - primary key
AK_Product_Name       - alternative key (unique column)
FK_Product_CategoryID - foreign key

Indexes

IX_Product_Price

Answer 14

We are naming columns with the first letter of the table name, underscore, and the column itself. for example in the blog_msgs table we are using m_id, m_blog, m_title, m_content and in the blogs table we are naming like b_id, b_title, b_author.

This good because of that this method is short, and you can differ between the blog title and the post title by the first letter, the title of the blog is b_title and the title of the blog post is m_title (m for message).

Answer 15

I'd go with the second. It's not a matter of Don't Repeat Yourself (it's not code). Product fields are only useful with products, and nothing else.

See ISO/IEC 11179 for more ideas why.

Answer 16

No one has mentioned this so I thought I would - I struggle with things like the "name" column. I find myself prefixing generic columns like "name" because it's so ambiguous. But I do that for readability, not as a blanket prefixing rule.