wood burning stoves 2.0*
The moose likes Cattle Drive and the fly likes MySQL naming conventions? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » This Site » Cattle Drive
Bookmark "MySQL naming conventions?" Watch "MySQL naming conventions?" New topic
Author

MySQL naming conventions?

Katrina Owen
Sheriff

Joined: Nov 03, 2006
Posts: 1357
    
  17
Is the ALL CAPS database names, table names, column names a convention for MySQL, or is it ok to use



and



?
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
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."


JavaBeginnersFaq
"Yesterday is history, tomorrow is a mystery, and today is a gift; that's why they call it the present." Eleanor Roosevelt
Jim Yingst
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
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.


"I'm not back." - Bill Harding, Twister
Marilyn de Queiroz
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
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
Wanderer
Sheriff

Joined: Jan 30, 2000
Posts: 18671
[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
Sheriff

Joined: Jul 22, 2000
Posts: 9044
    
  10
[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
village idiot
Bartender

Joined: Mar 15, 2001
Posts: 1194
I wonder if conversations such as this can be overheard in the hallways of the UN?
Katrina Owen
Sheriff

Joined: Nov 03, 2006
Posts: 1357
    
  17
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:



Since when I typed that code I saw:



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

paul wheaton
Trailboss

Joined: Dec 14, 1998
Posts: 20494
    ∞

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?


permaculture Wood Burning Stoves 2.0 - 4-DVD set
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: MySQL naming conventions?
 
Similar Threads
how to create MySql stored procedure?
Getting all the database names using java with MySql
Listing tables in mySQL
retrieve table name
Retrieve Stored Procedure Code