The best way to name tables

Question

hi just a quick question, is it better to use underscore whilst naming tables or is it better to use camelcase.

example table_name or tableName which one is better? and if there is a reason to be using either what is it?

many thanks.

Answer 1

I say use whatever naming convention you use in the code that accesses it for high-level structures (like classes).

For example, if you encapsulate the data* in a class called UserInfo, the table should be called UserInfo as well.

* You are encapsulating the data, right?

Answer 2

Rather than provide you with a brief feedback on a few reasons for this and that I will provide a link to Pinal Dave's SQL coding guidelines. They've been well thought out, explained and seem to follow my own preferences on how things should be named and used etc.

Enjoy!

Answer 3

My opinion on this is that you should do whatever feels natural for you, if the best naming convention in the world (for the person who wrote it) will not feel natural and you will have to read the document each time you are designing a database than that's bad.

You should do whatever is convenient for you and what flows out of your keyborad better.

In my experience, I moved to ROR from .net a while back and was caught in the way I always saw people do these things and underscored my tables but after a while I neglected this option for my good-old SQL naming convenience.

the same for columns.

Good luck

Answer 4

Everything I have read indicates the best convention is to stick to lower-case for both database names and table names. If you need to put multiple words together, separate them with underscores -- avoid hyphens in table names or else you will have to back-tick all of your table names and database names every time you reference them.

Also, the following configuration parameter is probably relevant to what you are asking:

mysql> show global variables like 'lower%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 0     |
+------------------------+-------+
2 rows in set (0.00 sec)

Regarding field names, I tend to use camel-case.

Answer 5

My preference is to use descriptive names and use Pascal casing (TableName).

Avoid abbreviations, and if you do have to use an abbreviation treat it like a word (use DaylightSavingsTimeInfo or DstInfo instead of DSTInfo).

Naming isn't something that has a single right answer, so the best thing is to pick something and stay consistent. The worst thing is to have a mish-mash of many standards.

Answer 6

Some database engines are case-insensitive, and indeed will convert names to all lower-case on some outputs. Because of this, I've taken to using underscores between words and using all lower case.

MySQL respects case as I recall so this isn't an immediate issue for you. But I got burned on this once when I had to port a database from one engine to another -- I think it was from MySQL to Oracle but I wouldn't swear to that -- and all our camelCase names suddenly became run-together names.

I'll also second Rob Boek on the most important thing being consistency. And while we're on the subject, can I make a tangential comment about being consistent in field names? I'm working with a system now where I found that the field "prodid" in one table is in fact the exact same contents as "style" in another, and another system that has "delivereddate" in one table and "datedelivered" in another.

Answer 7

I believe that many RDBMSes ignore case, so using camel-case naming schemes may simply not work.

The important thing is to have the column names and table names be descriptive, no matter what nit-picky syntax you decide. If they are descriptive of their data, no one will care whether you used camel-case, underscores, etc.