9

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.)?

dallin
  • 8,775
  • 2
  • 36
  • 41
  • MySQL (depending on a combination of various configuration parameters, storage engine and filesystem) and SQL Server are the only "case-preserving" DBMS. All the others either comply with the standard and fold all non-quoted identifiers to uppercase, or slightly deviated and fold to lowercase. –  Apr 29 '13 at 21:45

3 Answers3

12

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • The SQL standard actually requires folding to uppercase for unquoted identifiers. –  Apr 29 '13 at 21:44
  • So how does this case folding affect the programmer? Is this something behind the scenes that doesn't matter to the programmer as long as he is consistent with his case, or are there times where he could create a table called MyTable and the database would require him to access that table in the future as MYTABLE or mytable due to the folding? Or could he have a table called MyTable on one OS or database engine and transfer it to another and find out he now has to call it MYTABLE? – dallin Apr 29 '13 at 21:51
  • @a_horse_with_no_name Didn't know that. DB2 is the only database I've used that folds to uppercase, but it's been *many* years since I used it, so things might be different now. All others I've used preserve or fold to lowercase. It would be interesting to collate list of dbs and how they handle case. – Bohemian Apr 29 '13 at 21:55
  • 1
    @dallin: as long as you don't use quoted identifiers you don't need to care `Foo`, `foo`, `FOO`, `fOO` is all the same "name" (with a standard compliant datatabase). `"FOO"` and `foo` are only the same if the DBMS folds to uppercase, and `"foo"` and `FOO` are only the same if the DBMS folds to lowercase. One of the reasons to *never* use quoted identifiers. –  Apr 29 '13 at 21:58
  • 1
    @a_horse_with_no_name re never using quoted names - I wholeheartedly agree. IMHO using them is practically a bug. Certainly a time bomb. And what's with MSSQL allowing, and even encouraging, *spaces* in names?! `create table example ([totally insane] boolean not null default true)`. I used to think that the MS developers were morons, until I realised that it's (evilly brilliantly) not portable - once you have them by the syntax, their hearts and minds (and licences) will follow. – Bohemian Apr 30 '13 at 15:14
4

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.

nido
  • 521
  • 3
  • 5
  • It also depends on innodb_file_per_table setting which - if turned off - makes case-sensitivity *not* depending on the filesystem and then there is lower_case_tablenames which brings another dimension into this behaviour. –  Apr 29 '13 at 21:47
  • Are you absolutely sure, a_horse_with_no_name? Even with innodb_file_per_table turned off, a folder is created with the database name and in that several files named after the table, both of which are at the mercy of the underlaying filesystem. – nido Apr 29 '13 at 22:03
  • To my understanding InnoDB will create *one* container file that will contain **all** table definitions. –  Apr 29 '13 at 22:06
0

SQL is case insensitive, unless you are using delimited identifiers. The example above compares a normal identifier with a delimited one. Identifier rules go out the window when using delimited identifiers; this is a valid identifier "4argo @##$@ yourself".

Words strung together are harder to read than words separated, and they are more prone to spelling errors, especially if plurals are sometimes involved.

Delimited identifiers allow you to mimic case-sensitive application languages, but they can cause difficulties in things like metadata searches. You can easily end up with vendor-specific behavior, which is an obvious pain.

Some DBMS were implemented before SQL was standardized to fold to uppercase; they will never change. (My guess is IBM won an argument at some meeting and made it uppercase.) And, they should not have to, as long as case insensitivity is handled correctly.

It's a bad idea to model your schema based on how the particular DBMS you are using is implemented.

Chris Golledge
  • 350
  • 4
  • 7