Results 1 to 4 of 4
Jan 22, 2012, 00:51 #1
MySQL table and column Naming Conventions
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.
Jan 22, 2012, 02:31 #2
- Join Date
- Jul 2002
- Toronto, Canada
- 60 Post(s)
- 3 Thread(s)
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
Jan 22, 2012, 02:59 #3
- Join Date
- Jan 2012
- 0 Post(s)
- 0 Thread(s)
Jan 22, 2012, 12:50 #4
- Join Date
- Aug 2008
- The Netherlands
- 153 Post(s)
- 2 Thread(s)
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.Rémon - Hosting Advisor
SitePoint forums will switch to Discourse soon! Make sure you're ready for it!
Minimal Bookmarks Tree
My Google Chrome extension: browsing bookmarks made easy