...

View Full Version : MySQL table and column Naming Conventions



phantom007
01-22-2012, 07:52 AM
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.

Old Pedant
01-22-2012, 08:09 AM
I think there's a clue for you in the fact the you read conflicting advice: There AREN'T any industry-wide standards. Period.

There might be standards in a given company that all programmers for that company are expected to follow. But that's about it.

The notion of three letter prefixes (e.g, "tbl" and "int" and "str") comes from Hungarian Notation, which was all the rage in the 80s and early 90s, especially since MicroSloppy used it throughout the company (to distraction!). But even the Slop Shop finally saw the light and has, for the most part, stopped using Hungarian in all the ASP.NET code. (Personally, I always hated it, and even during my short stint at MacroSloop I avoided it. There are many reasons it's dumb...not worth going into here...but I think it is especially dumb in database usage. But you will still find shops today that insist on using it.)

Unless you are working for a company that has required coding standards, use what makes sense to you. Period.

(I will temper that a bit by saying that the one convention I *really* like is naming primary and foreign keys the same, when possible. So yes, I would tend to have memid primary key in a members table and I tend to avoid the use of id alone. But I'm not fanatic about it. If you can't stand doing that, don't.')

djh101
01-22-2012, 08:12 AM
I've never heard of anyone appending the table name to its columns. That seems a little redundant. I name my columns by what they are- id, name, email, etc. There aren't really any universal naming conventions, as far as I'm aware- just use whatever works.

phantom007
01-22-2012, 08:26 AM
What about table names? Do you prefer using singular or plural names?

Old Pedant
01-22-2012, 08:52 AM
What about table names? Do you prefer using singular or plural names?

Oh, yes. Definitely. Singular or plural names. I wouldn't even consider anything else.

[That's almost not a joke. Some languages--e.g., Russian--have singular, dual, and plural. In Russian, in some grammar constructions the numbers 2, 3, and 4 use different rule than do larger numbers.]

KuriosJon
01-22-2012, 02:03 PM
No matter what conventions you decide to adopt, the most important thing to do is be consistent. You'll give yourself headaches if you start mixing things.

As far as table names go, I'm seeing a lot of people move towards singular table names. One reason for that is that you can often think of a row in a table as an object.

users.name doesn't make as much sense as user.name when you're treating it as an object.

Regardless, Old Pedant is right when he said there is no industry standard. If you're working in existing code or working for someone else, use the existing conventions. If you're writing your own code, use what you like best, but be consistent.

phantom007
01-22-2012, 03:57 PM
Thanks for the replies guys. I appreciate it.

Old Pedant
01-23-2012, 12:47 AM
No matter what conventions you decide to adopt, the most important thing to do is be consistent.

A-double-men!



As far as table names go, I'm seeing a lot of people move towards singular table names. One reason for that is that you can often think of a row in a table as an object.

users.name doesn't make as much sense as user.name when you're treating it as an object.

Interesting viewpoint. Personally, I don't see any reason that the table names need to match the row names.

I often use "people" as a table name and "person" as a record/row name.

It doesn't bother me to say WHERE people.role = Administrator in my SQL queries, but I can certainly understand that viewpoint.

But never mind...consistency truly is the key.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum