Should I write table and column names ALWAYS lower case?
DatabaseDatabase DesignDatabase Problem Overview
I wonder if it's a problem, if a table or column name contains upper case letters. Something lets me believe databases have less trouble when everything is kept lower case. Is that true? Which databases don't like any upper case symbol in table and column names?
I need to know, because my framework auto-generates the relational model from an ER-model.
(this question is not about whether it's good or bad style, but only about if it's a technical problem for any database)
Database Solutions
Solution 1 - Database
As far as I know there is no problem using either uppercase and lowercase. One reason for the using lower case convention is so that queries are more readable with lowercase table and column names and upper case sql keywords:
SELECT column_a, column_b FROM table_name WHERE column_a = 'test'
Solution 2 - Database
It is not a technical problem for the database to have uppercase letters in your table or column names, for any DB engine that I'm aware of. Keep in mind many DB implementations use case sensitive names, so always refer to tables and columns using the same case with which they were created (I am speaking very generally since you didn't specify a particular implementation).
For MySQL, here is some interesting information about how it handles identifier case. There are some options you can set to determine how they are stored internally. http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html
Solution 3 - Database
The SQL-92 standard specifies that identifiers and keywords are case-insensitive (per A Guide to the SQL Standard 4th edition, Date / Darwen)
That's not to say that a particular DBMS isn't either (1) broken, or (2) configurable (and broken)
From a programming style perspective, I suggest using different cases for keywords and identifiers. Personally, I like uppercase identifiers and lowercase keywords, because it highlights the data that you're manipulating.
Solution 4 - Database
SQL standard requires names stored in uppercase
The SQL standard requires identifiers be stored in all-uppercase. See section 5.2.13 of the SQL-92 as quoted from a draft copy in this Answer on another Question. The standard allows you use undelimited identifiers in lowercase or mixed case, as the SQL processor is required to convert as needed to convert to the uppercase version.
This requirement presumably dates back to the early days of SQL when mainframe systems were limited to uppercase English characters only.
Non-issue
Many database ignore this requirement by the standard.
For example, Postgres does just the opposite, converting all unquoted (“undelimited”) identifiers to lowercase — this despite Postgres otherwise hewing closer to the standard than any other system I know of.
Some databases may store the identifier in the case you specified.
Generally this is a non-issue. Virtually all databases do a case-insensitive lookup from the case used by an identifier to the case stored by the database.
There are occasional oddball cases where you may need to specify an identifier in its stored case or you may need to specify all-uppercase. This may happen with certain utilities where you must pass an identifier as a string outside the usual SQL processor context. Rare, but tuck this away in the back of your head in case you encounter some mysterious "cannot find table" kind of error message someday when using some unusual tool/utility. Has happened to me once.
Snake case
Common practice nowadays seems to be to use all lowercase with underscore separating words. This style is known as Snake case.
The use of underscore rather than Camel case helps if your identifiers are ever presented as all uppercase (or all lowercase) and thereby lose readability without the word separation.
Bonus Tip: The SQL standard (SQL-92 section 5.2.11) explicitly promises to never use a trailing underscore in a keyword. So append a trailing underscore to all your identifiers to eliminate all worry of accidentally colliding.
Solution 5 - Database
As far as I know for a common L.A.M.P. setup it won't really matter - but be aware that MySQL hosted on Linux is case sensitive!
To keep my code tidy I usually stick to lower case names for tables and colums, uppercase MySQL-Code and mixed Upper-Lower-Case variables - like this:
SELECT * FROM my_table WHERE id = '$myNewID'
Solution 6 - Database
I use pascal case for field names lower case for table names (usually) as follows:
students
--------
ID
FirstName
LastName
Email
HomeAddress
courses
-------
ID
Name
Code
[etc]
Why is this cool? because it's readable, and because I can parse it as:
echo preg_replace('/([a-z])([A-Z])/','$1 $2',$field); //insert a space
NOW, here's the fun part for tables:
StudentsCourses
--------------
Students_ID
Courses_ID
AcademicYear
Semester
notice I capitalized S and C? That way they point back to the primary table(s). You could even write a routine to logically parse db structure this way and build queries automatically. So I use caps in tables when they are JOIN tables as in this case.
Similarly, think of the _ as a -> in this table as: Students->ID and Courses->ID Not student_id - instead Students_ID - the cognate of the field matches the exact name of the table.
Using these simple conventions produces a readable protocol which handles about 70% of your typical relational structure.
Solution 7 - Database
Whatever you use, keep in mind the MySQL on Linux is case sensitive, while on Windows it is case insensitive .
Solution 8 - Database
No modern database cannot handle upper or lower case text.
Solution 9 - Database
If you're using postgresql and PHP, for instance, you'd have to write your query like this:
$sql = "SELECT somecolumn FROM \"MyMixedCaseTable\" where somerow= '$somevar'";
"Quoting an identifier also makes it case-sensitive, whereas unquoted names are always folded to lower case. For example, the identifiers FOO, foo, and "foo" are considered the same by PostgreSQL, but "Foo" and "FOO" are different from these three and each other. (The folding of unquoted names to lower case in PostgreSQL is incompatible with the SQL standard, which says that unquoted names should be folded to upper case. Thus, foo should be equivalent to "FOO" not "foo" according to the standard. If you want to write portable applications you are advised to always quote a particular name or never quote it.)" http://www.postgresql.org/docs/8.4/static/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
So, sometimes, it depends on what you are doing...
Solution 10 - Database
The column names which are mixed case or uppercase have to be double quoted in PostgreSQL. If you don't want to worry about it in the future, name it in the lower case.
MySQL - the columns are absolutely case insensitive. And it can lead to problems. Say someone has written "mynAme" instead of "myName". The system would work fine, but once some developer would go searching for it through the source code, they might overlook it, and you all get in trouble.
Solution 11 - Database
Think this is worth emphasizing: If a binary or case-sensitive collation is in effect, then (at least in Sql Server and other databases with rich collation features) identifiers and variable names WILL be case sensitive. You can even create tables whose names differ only in case. (—I am not sure the info above about the sql-92 standard is correct—if so, this part of the standard is not widely followed.)