MySQL table and column Naming Conventions

[phantom007]

Hello MySQL Gurus,

I have been reading articles, blogs, books about the naming conventions for designing a mysql database.

Every time I read a new article / blog, I find the authors are suggesting different types of naming conventions which is confusing me a lot.

For instance, i read an article on the Internet that says that table names should be singular, others say table name should be plural while other say every table name should have a minimum of 3 chars of acronym that represents a short name for that table.

In terms of column names, some say the primary key should be just “id”, while others say primary keys should have the table name followed by the id like “user_id”, or the table acronym followed by id like “usr_id”.

So, if there is a message table, the column names should be such as message_id, message_title, message_description

and for a comments table:
comments_id, comments_title, comments_description

I am totally confused as to what conventions should I follow and that should be industry standard.

Can you guys please suggest me the right one?

Many thanks in advance.

[r937]

there are no industry standards, i’m afraid

further, it doesn’t really matter what table and column names you choose, their behaviour will be the same regardless

however, there are some conventions which are more useful, or perhaps more aesthetically pleasing

for instance, embedding a table acronym at the start of all of its column names is, in my opinion, counter-productive, because it adds noise to the signal, as it were

for instance, suppose you had a threads table and a comments table – with the prefixing convention, you’d have thread_id and thread_title, and comment_id and comment_comment (see, already it’s getting repetitive)

but what about the foreign key in the comments table? would you call it thread_id, or comment_thread_id?

the convention i prefer does not use table prefixes on column names ~except~ for foreign keys, so in the threads table i would use id and title, and in the comments table id and comment, with thread_id being the foreign key to the threads table

and i prefer a plural table name, because it represents a collection of rows, such as threads and comments, rather than a single instance

in the end, though, it’s all your choice

[ebiwari]

@r937

there are no industry standards, i’m afraid

further, it doesn’t really matter what table and column names you choose, their behaviour will be the same regardless

however, there are some conventions which are more useful, or perhaps more aesthetically pleasing

for instance, embedding a table acronym at the start of all of its column names is, in my opinion, counter-productive, because it adds noise to the signal, as it were

for instance, suppose you had a threads table and a comments table – with the prefixing convention, you’d have thread_id and thread_title, and comment_id and comment_comment (see, already it’s getting repetitive)

but what about the foreign key in the comments table? would you call it thread_id, or comment_thread_id?

the convention i prefer does not use table prefixes on column names ~except~ for foreign keys, so in the threads table i would use id and title, and in the comments table id and comment, with thread_id being the foreign key to the threads table

and i prefer a plural table name, because it represents a collection of rows, such as threads and comments, rather than a single instance

in the end, though, it’s all your choice

I totally agree with u. But if u ask me, I prefare table naming nomaclature i.e if u hav a table ‘comment’… comment_id, comment_name… Whatsoever style u used. Ithink consistancy is bottom line…Stick to anyone u choose…

thanks

[rpkamp]

I don’t like the prefixing either. It’s extra stuff you need to type that doesn’t serve any purpose. When I look at the column in the comments table it’s clear to me that that column belongs to the comments table, I don’t need a prefix to tell me that.
I also wouldn’t name my cat (if I had one) “Cat Bob”; I can see it’s a cat, no need to call him a cat too. I’ll just call him Bob.

Whatever you do, please don’t prefix your tables with tbl_; there’s really no point whatsoever.