11 Replies - 22601 Views - Last Post: 22 January 2012 - 02:44 PM

#1 cancer10  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 52
  • Joined: 12-July 08

MySQL table and column Naming Conventions

Posted 21 January 2012 - 11:52 PM

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.

Is This A Good Question/Topic? 1
  • +

Replies To: MySQL table and column Naming Conventions

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9196
  • View blog
  • Posts: 34,533
  • Joined: 12-June 08

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 01:54 AM

Quote

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.


Right there should suggest that people have different views of how to name their tables.. some are hold overs from jobs, some are from personal views.. and some are just plain weird. There is no tried or true method.

I would be less concerned about the names and more concerned that you get the right knowledge for relational databases down.
Was This Post Helpful? 1
  • +
  • -

#3 thrca  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 28
  • View blog
  • Posts: 65
  • Joined: 21-January 12

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 02:24 AM

As modi suggested, I agree that it is more important to understand normalization and relationships than naming conventions.. Most of the naming conventions are personal preference.

That being said, I personally choose to name my primary key fields as tablename_id, such as users.user_id, addresses.address_id, etc. This prevents me from having an `id` field in every table, which can get ambiguous and confusing very quickly, especially when dealing with foreign key relationships.

For example, consider the following tables:
CREATE TABLE users (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username TEXT,
  password TEXT
);
CREATE TABLE addresses (
  address_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,
  address_1 TEXT,
  address_2 TEXT,
  city TEXT,
  state TEXT,
  zip INTEGER,
  CONSTRAINT user_id_fk FOREIGN KEY (user_id) REFERENCES users (user_id)
    MATCH SIMPLE ON DELETE CASCADE ON UPDATE CASCADE
);



I find that it is much easier to see at a glance when looking at table definitions that there is a foreign key constraint and that the columns are named the same thing.

As far as naming each column with something to do with the table name, I find this to be a horrible practice which a lot of people seem to think is a good idea. Why is it a horrible practice? It encourages people to be lazy and not use fully qualified references to their columns.

No fully qualified column references, if you were to add a field in users table called 'city', your application would explode!
SELECT users.user_id,username,city,state FROM users LEFT JOIN addresses ON addresses.user_id=users.user_id;



Better, we are now qualifying the column references, but dang is it a lot to read and type...
SELECT users.user_id,users.username,addresses.city,addresses.state FROM users LEFT JOIN addresses ON addresses.user_id=users.user_id;



Best, now we specify aliases for the table. This resolves ambiguity, and makes it easier to read. Future programmers don't have to examine all the schema to see which table you are getting your columns from.
SELECT u.user_id,u.username,a.city,a.state FROM users u LEFT JOIN addresses a ON a.user_id=u.user_id;



And these samples bring me to another point.. Always use explicit joins, unless you want your application to explode and be hard to figure out why at some point in the future...
An implicit join (which gets really hairy when you join lots of things)
/* PLEASE DONT DO THIS, ITS FOR EXAMPLE ONLY */
SELECT users.user_id,username,city,state FROM users,addresses WHERE users.user_id=addresses.user_id;


This post has been edited by thrca: 22 January 2012 - 02:26 AM

Was This Post Helpful? 3
  • +
  • -

#4 cancer10  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 52
  • Joined: 12-July 08

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 02:49 AM

Assuming we add a "city" column in the users table, then Using this query,


SELECT users.user_id,users.username,users.city, addresses.city,addresses.state FROM users LEFT JOIN addresses ON addresses.user_id=users.user_id;



When a result is returned, how do you determine which city column belongs to which table?
Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 795
  • View blog
  • Posts: 1,681
  • Joined: 30-January 09

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 02:53 AM

This tends to be a contentious topic, unnecessarily in my opinion. There are no "wrong" or "right" naming conventions, so long as they are valid (e.g. - no two columns in the same table have the same name). If you want to differentiate between naming conventions, thinking of them as either "appropriate" and "inappropriate" is worthy, as well as thinking of them as either "conformative" or "non-conformative".

Instead of choosing a naming convention based on whether someone on the internet says it's right or wrong, choose a naming convention based on some or all of the following reasons:
  • Does it lend itself to coding consistency?
  • Is there consistency in names of columns?
  • Is there consistency in names of columns that reference other columns (i.e. - foreign keys)?
  • Is it consistent linguistically?
  • Is it consistent syntactically?
  • Is it informative?
  • Is it meaningful?
  • Does it avoid ambiguity?


There are likely other considerations, but those are a few off the top of my mind.

For MySQL, there is a particular very important consideration you should know about. Identifier case sensitivity is dependant upon operating system. I found this out the hard way. Thankfully it can be fixed by upgrading your MySQL (can't recall which exact version, but it is 5.1.something). An article on it can be found here.

To give you some insight into picking a naming convention, I use the following naming conventions:
  • Table names are preceded by table type, then an underscore, then camel case for the particular name. Case is upper for the first letter of each word, lower for subsequent letters. For example, my lookup tables have names like Lookup_AdminState and Lookup_ProjectType. Some of my project tables have names like Project_Owners, Project_Attachments, and Project_LogEntries.
  • All primary keys in tables are named 'ID'.
  • Columns that have FK constraints against lookup tables use the referenced lookup table name followed by ID. For instance, a column that references the column `Lookup_AdminState`.`ID` will be named 'AdminStateID'.
  • Columns that have FK constraints against non-lookup tables attempt to fully resolve the table name followed by ID. For instance, a column that references `Project_Attachments`.`ID` will be named 'ProjectAttachmentID'.
  • If more than one column has a FK constraint against the same column, they must be given distinct names that do not follow the previous two conventions. For example, if I have two columns that reference `Users`.`ID`, they may be given names such as 'CreatedByUserID' and 'UpdatedByUserID'.

Now, more importantly, here;s some insight into why I have chosen each convention:
  • Retrieving all lookup tables from the INFORMATION SCHEMA DB is easy, as I simply search for all tables whose names begin with 'Lookup_'.
  • Having a common PK name means that I can use the same templated query to reference any number of tables. For instance, I have a common method called 'GetLookupValue', which gets passed the table name, the ID to lookup, and the name of the column to be returned, so I don't need to rewrite this method for every table.
  • It is easy to see what the column references
  • As per (3)
  • To avoid ambiguity

Another important point is that this naming convention is used throughout my entire codebase, not just the MySQL, but also the PHP and jQuery/Javascript code. This means that when I pass data around between each aspect of my application, I don't have to rename it at each stage to conform to another convention.

I recommend reading the Wikipedia article on the subject, as it will help you understand the right and wrong reasons for choosing a naming convention.

This post has been edited by e_i_pi: 22 January 2012 - 04:11 AM

Was This Post Helpful? 4
  • +
  • -

#6 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 04:04 AM

The only thing that really matters when it comes to naming conventions is to be consistent. Few things are more annoying than projects where the author(s) seem to have picked a convention at random each morning, or where each author of a large project is using a different convention than the others. - Everything else is just personal preference. Although, a lot of people seem to think their own personal preference is the only way, and everything else is just ignorance. Don't pay attention to such things. Find what works for you; what makes most sense to you.


... Except for uncommon acronyms. I fail to see why those are used. - Really, it's not making anything clearer or easier to read, it's not increasing performance, and it's not saving you any space worth mentioning. What is the point? -- This goes extra for "usr", "tbl", and other four letter words. Is that one extra letter really that hard to fit in there?!

View Postcancer10, on 22 January 2012 - 09:49 AM, said:

Assuming we add a "city" column in the users table, then Using this query,


SELECT users.user_id,users.username,users.city, addresses.city,addresses.state FROM users LEFT JOIN addresses ON addresses.user_id=users.user_id;



When a result is returned, how do you determine which city column belongs to which table?

The only way would be the order of the columns. MySQL has no problem returning columns with identical column names, as long as it can determine which is which in the query itself. - If you want to make sure they have unique names in the output, use aliases.
SELECT u.city AS user_city, a.city AS address_city
FROM users AS u
LEFT JOIN address AS a
    ON a.user_id = u.user_id;


Was This Post Helpful? 2
  • +
  • -

#7 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5823
  • View blog
  • Posts: 12,675
  • Joined: 16-October 07

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 05:04 AM

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username TEXT,
  password TEXT
);
CREATE TABLE addresses (
  address_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,



I'd avoid plurals in table names. Two reasons. First, tables, by their nature contain multiple items, so it's implicit in the design. Second, consistent names are a lot more important than grammar.


CREATE TABLE user (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username TEXT,
  password TEXT
);
CREATE TABLE address (
  address_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,



Think of it this way, the user table contains user rows.

I'd also put an unique constraint on username. Think about it... I'm not saying I wouldn't use user_id, it's just that much easier to throw around. But respect the natural key of the table and make sure it stays that way.
Was This Post Helpful? 1
  • +
  • -

#8 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3719
  • View blog
  • Posts: 5,990
  • Joined: 08-June 10

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 05:26 AM

I've always been kind of split on the plural table names issue. On one hand, a table contains many "users", so even though it's implicit, it somehow feels wrong not to spell it out. (Perhaps just a leftover reflex from old-school spreadsheets like Excel...) One the other hand, using SELECT users.id makes less sense than saying SELECT user.id, and the OOP programmer in me would agree with that way of thinking about it.

I usually end up going with the plural names though. If only because it conflicts less with keywords. (Poor reason, I know, but something has to tip the scales :))
Was This Post Helpful? 1
  • +
  • -

#9 thrca  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 28
  • View blog
  • Posts: 65
  • Joined: 21-January 12

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 10:19 AM

Regarding plurality in table names... In my example, it was a consistency carry over from my normal conventions.

The reason I use plurality in names is to help me distinquish relationships.. In my example, if addresses were only used for users, I would have actually named them

users and user_addresses, indicating that multiple addresses belong to a single user. If it was a 1 to 1 relationship, it would be called users and user_address.

Assuming addresses are used for something else also other than users, say, companies or something... I would have "users", "addresses", "companies", "user_addresses_map", "company_addresses_map"

Its up to everyone how they prefer to do this stuff, but after 15+ years, this is what works for me and is easy for the rest of my team to understand. Since my primary work is on a database that is 30+Gb and has several thousand tables and relationships, its pretty important that we can understand each others work.
Was This Post Helpful? 2
  • +
  • -

#10 cancer10  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 52
  • Joined: 12-July 08

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 10:42 AM

View Postbaavgai, on 22 January 2012 - 05:04 AM, said:

CREATE TABLE users (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username TEXT,
  password TEXT
);
CREATE TABLE addresses (
  address_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,



I'd avoid plurals in table names. Two reasons. First, tables, by their nature contain multiple items, so it's implicit in the design. Second, consistent names are a lot more important than grammar.


CREATE TABLE user (
  user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  username TEXT,
  password TEXT
);
CREATE TABLE address (
  address_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT,



Think of it this way, the user table contains user rows.

I'd also put an unique constraint on username. Think about it... I'm not saying I wouldn't use user_id, it's just that much easier to throw around. But respect the natural key of the table and make sure it stays that way.



MySQL does not allow you to have table names like "user", "order", "name" since these are reserved words in mysql. Which is why you need to use plurals sometimes "users", "orders" etc or have an alternate name for such tables.
Was This Post Helpful? 0
  • +
  • -

#11 thrca  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 28
  • View blog
  • Posts: 65
  • Joined: 21-January 12

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 02:13 PM

While
CREATE TABLE user (...


would not work, I believe you can do
CREATE TABLE `user` (...

Was This Post Helpful? 0
  • +
  • -

#12 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5823
  • View blog
  • Posts: 12,675
  • Joined: 16-October 07

Re: MySQL table and column Naming Conventions

Posted 22 January 2012 - 02:44 PM

View Postcancer10, on 22 January 2012 - 01:42 PM, said:

MySQL does not allow you to have table names like "user", "order", "name" since these are reserved words in mysql.


Then you would be ill advised to try to use them. ;)

As noted, you can actually use any name you like with back ticks. Personally, I like to avoid the requirement of back ticks, if I can.

Saying you must use a plural of a reserved word strikes me as a little silly. What if there's a plural reserved word? There is always an alternative; you are free to use whatever you like. Is "login" a reserved word? How about "app_user"?

The names you use are mostly personal preference. The reason I point out the plural thing is that is does come up in various best practice literature. My issue is with a table name being foos and the identifier being foo_id, rather than foos_id. It might be intuitive, it might not. You're declaring symbols that have meaning, but sometimes they don't really have meaning and now what do you do? Now table users has user_id and table moose has moose_id and I'm confused.

There is no consensus. No one best way. You will ultimately use the system you feel works best for you. The only real challenge is being consistent about it.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1