MySQL naming conventions?

[Katrina Owen]

Is the ALL CAPS database names, table names, column names a convention for MySQL, or is it ok to use

CREATE DATABASE soup;

and

SELECT * FROM videos;

?

[Marilyn de Queiroz]

It depends on several things, one of which is the OS you are developing on. I suggest that it is better to stick to the convention of always putting table names, etc in upper case.

The documentation states, "To avoid problems caused by such differences, it is best to adopt a consistent convention, such as always creating and referring to databases and tables using loweruppercase names. This convention is recommended for maximum portability and ease of use."

[Jim Yingst]

Hi, pardon the intrusion from a non-cattle-driver, but this is an issue I was wondering about recently, so I thought I'd ask.

To be clear, the documentation suggests using lowercase, and says that's for maximum portability and ease of use. You (Marilyn) have stricken out lower and replaced it with upper, correct? Is there any reason why uppercase would be more portable, or confer other benefits?

From reading the docs, it seems that some OS's convert table names to lower case by default, and some don't. None seem to convert to upper case by default. Thus, if on uses lower case names exclusively, it would seem there is less chance of a mismatch later (e.g. if you use Java's .equals() method to compare two table names, rather than letting mysql do the comparison). Am I overlooking something? Are there situations where uppercase is more desirable for some reason?

The only reason I can think of is to conform to an existing group aesthetic - i.e. if a company has just always used uppercase, and that's their standard, then you should continue to use that while working for that company. Much like many other code style issues, it can be an arbitrary choice where consistency is more important than other considerations.

[Marilyn de Queiroz]

JY: To be clear, the documentation suggests using lowercase, and says that's for maximum portability and ease of use. You (Marilyn) have stricken out lower and replaced it with upper, correct? Is there any reason why uppercase would be more portable, or confer other benefits?

MQ: I think it says to adopt a consistent convention, a convention such as lowercase. I don't read this as saying that lower case is better than upper case or vice-versa. To the best of my knowledge, Windows converts all filenames to upper case. At least it used to display all filenames in upper case.

JY: From reading the docs, it seems that some OS's convert table names to lower case by default, and some don't. None seem to convert to upper case by default.

Maybe you're reading a different part of the docs, but I'm seeing that the default behavior of MySql in windows is, "MySQL converts all table names to lowercase on storage and lookup. This behavior also applies to database names and table aliases," not the default behavior of the OS itself. And the default behavior of MySql on Unix is, "Table and database names are stored on disk using the lettercase specified in the CREATE TABLE or CREATE DATABASE statement. Name comparisons are case sensitive."

JY: Thus, if on uses lower case names exclusively, it would seem there is less chance of a mismatch later (e.g. if you use Java's .equals() method to compare two table names, rather than letting mysql do the comparison). Am I overlooking something? Are there situations where uppercase is more desirable for some reason?

I think when you are using Java equals(), rather than depending on convention, you would be better off to use something like equalsIgnoreCase(), toUpperCase() or toLowerCase(). If the sql is wrong, you'll probably get a sql exception, but if the equals() is wrong, it will just be false.

JY: The only reason I can think of is to conform to an existing group aesthetic - i.e. if a company has just always used uppercase, and that's their standard, then you should continue to use that while working for that company. Much like many other code style issues, it can be an arbitrary choice where consistency is more important than other considerations.

Maybe that's why I prefer uppercase, because I've seen so much of it. I agree that consistency is the most important consideration. It's easier for me if all my students use upper case rather than some using upper, some using lower, and some using camelCase.

[Jim Yingst]

[MdQ]: I think it says to adopt a consistent convention, a convention such as lowercase. I don't read this as saying that lower case is better than upper case or vice-versa.

It's a little ambiguous, I suppose. "...it is best to adopt a consistent convention, such as always creating and referring to databases and tables using lowercase names. This convention is recommended for maximum portability and ease of use." Note that first they said a convention, later they said this convention. I believe that using this in the second half indicates they were talking about the specific convention they'd just mentioned, using lowercase. Not just any convention. Still, it's less clear than one might hope.

[MdQ]: To the best of my knowledge, Windows converts all filenames to upper case. At least it used to display all filenames in upper case.

Well, nowadays it certainly remembers the filenames in mixed case, able to display them in mixed case. It somehow ignores the case in most other circumstances - whether it does this by secretly converting to upper or lower case, or something else, I dont know. You're probably right about the past behavior; I remember older DOS screens, and converting everything to uppercaase is the simplest explanation for what we saw. Regardless though... those are files, not tables.

[MdQ]: Maybe you're reading a different part of the docs, but I'm seeing that the default behavior of MySql[...]

All right, I talked about OS behavior when I should have said, the behavior of mysql on the OS. You're correct. Either way though, my point is that in those cases where the table names get converted at all, they're converted to lower case, not upper, at least as far as mysql is concerned.

[MdQ]: I think when you are using Java equals(), rather than depending on convention, you would be better off to use something like equalsIgnoreCase(), toUpperCase() or toLowerCase(). If the sql is wrong, you'll probably get a sql exception, but if the equals() is wrong, it will just be false.

I agree you're better off with equalsIgnoreCase() or toSomeOtherCase() - when you remember to do it. But this is the sort of thing that can be easy to overlook. And it may run fine in Windows, but fail in Unix, or vice versa. That's where a consistent standard helps - and I believe it's more likely to help, at least in mysql, if the standard is lowercase, because mysql is more likely to convert to lowercase than to uppercase.

[MdQ]: Maybe that's why I prefer uppercase, because I've seen so much of it. I agree that consistency is the most important consideration. It's easier for me if all my students use upper case rather than some using upper, some using lower, and some using camelCase.

Agreed. Except I think you should all use lower.

OK, sorry to pester you, I was just interested in hearing if there were other issues involved. Thanks...

[Marilyn de Queiroz]

[JY]: I believe that using this in the second half indicates they were talking about the specific convention they'd just mentioned, using lowercase.

I can see how you could read it that way.

[JY]: Regardless though... those are files, not tables.

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

[JY]: Either way though, my point is that in those cases where the table names get converted at all, they're converted to lower case, not upper, at least as far as mysql is concerned.

Good point. I'll consider it a little more.

[JY]: OK, sorry to pester you.

You expect me to believe that?

[Carol Murphy]

I wonder if conversations such as this can be overheard in the hallways of the UN?

[Katrina Owen]

On my computer (linux), mySQL's own database and table names are in lowercase.

This and the following (from the JDBC assignments page) is what got me wondering about the whole thing in the first place:

Let's try a SQL command.
Type "CREATE DATABASE SOUP;" and press enter.
If you now type "SHOW DATABASES;" and press enter you should see: 
 
  +----------+
  | Database |
  +----------+
  | mysql    |
  | soup     |
  | test     |
  +----------+

Since when I typed that code I saw:

  +----------+
  | Database |
  +----------+
  | mysql    |
  | SOUP     |
  | test     |
  +----------+

I also like (when developing) to be able to differentiate quickly between what is SQL syntax and what is content-related:

SELECT email FROM users WHERE name="Katrina";

[paul wheaton]

One of the best things about java is portability. It is unfortunate that this is not a little more robust in the database stuff.

Some databases allow camelcase and some don't. My experience in the past is that those that don't, force everything to uppercase. I think that if they are gonna force a case, lowercase would be nicer. Perhaps some are making that switch now.

I like the idea that one could develop an app using a database and later somebody says they want to use a different database and the switch is easy. That seems like the sign of good engineering.

To that end, it seems wise to play to the lowest common denominator.

And to do that .... perhaps we need to gather data on the top five databases. I would guess mysql, postgres, oracle, sql server and ... perhaps db2?