The best way to name tables

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? Is there a reason to be using either, what is it?

Answer 1

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 2

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 lower-case as well.