Any reason to still use snake case for database ta

Question

Back when I started with database design, for some reason it was recommended that you always use snake case (my_table_name) for tables and columns. I think this was especially true in MySQL. The reasoning was there were instances where capitalization would be lost or enforced. Flash forward to today and I see a lot of people using Pascal Case ("MyTableName"), which I would prefer.

Is there any reason to still use snake case for table and column names? Are there any instances where capitalization could be lost or enforced (say if changing database engines, OS's, etc.)?

Answer 1

SQL is case-insensitive. Many databases fold names to lowercase when creating tables, so capitalisation is lost.

The only portable way to preserve "words" within names is to use snake case.

Answer 2

from http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html:

"In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system plays a part in the case sensitivity of database and table names. This means database and table names are not case sensitive in Windows, and case sensitive in most varieties of Unix. One notable exception is Mac OS X, which is Unix-based but uses a default file system type (HFS+) that is not case sensitive."

In short, it depends on the filesystem underneath the database.

Nowadays, most mysql servers run on linux systems which have an ext3/ext4/bttrfs/namesomeother filesystem which are case sensitive. FAT12, for example, was not case sensitive and not even case preserving, so the database MyDB may not be found by mysql after creation. Fat32 and HFS+ are not case sensitive, but it is case preserving; so you can get into trouble with Mydb and myDB.

So if you know your database may be hosted on a FAT12 system, you may still want to make sure you watch case.