White space in table names

Changhong · #1 (**permalink**) 04-05-06, 22:33

Hi all

I recently joined in a term and working on an ASP.NET project. I found the table names and column names in the database are having white spaces and I tried persuade the person who originally designed it to remove all the white spaces and using PascalCaseing, and it seemed to be hard to make her changing her idea. I am a newbie to database design, too, so I couldn't tell much about why white space is bad except it look ugly and no one have it. Is there any one having better idea?

The database now is on MS SQL server 2000, and we want it to be portable to Oracle and mysql( Oracle is a must ). This is a new database, only used by our project, and we have full control of it. We have already been working on it for nearly a year, and probably taking another year to complete, it has about 80 tables in database at moment, and we need add some more.

And another question: I having some primary keys in some table which I want them to be globally Unique. Is it a good idea to use GUID? Is there any problem with Oracle or MySql? Or I should use some different?

Thanks

andrewst · #2 (**permalink**) 04-06-06, 05:38

Using whitespace in table names in Oracle is a poor idea. It can be done, but only if you always enclose the table name in double quotes like this:

Code:

CREATE TABLE "Daft Table Name" ("Silly Column Name" INTEGER);
INSERT INTO "Daft Table Name" ("Silly Column Name") VALUES (123);
SELECT "Silly Column Name" FROM "Daft Table Name";

Also, once you start enclosing Oracle identifiers in quotes, they become case sensitive, so the following select would fail:

Code:

SELECT "silly column name" FROM "daft table name";

The usual Oracle convention for identifiers is to use underscores between words like this:

Code:

CREATE TABLE good_table_name (good_column_name INTEGER);

Now the identifiers are not case-sensitive, so any of the following will work:

Code:

select good_column_name from good_table_name;
SELECT GOOD_COLUMN_NAME FROM GOOD_TABLE_NAME;
select Good_Column_Name from Good_table_name;

Oracle always stores such unquoted identifier names in uppercase, which makes PascalCasing or camelCasing not such great choices either, if you want to be able to read the system catalog easily:

Code:

SQL> create table myCamelCaseTable
  2  ( camelCaseTableId integer
  3  , camelCaseDescription varchar2(30)
  4  , camelCaseStartDate date
  5  );

Table created.

SQL> desc myCamelCaseTable
 Name                            Null?    Type
 ------------------------------- -------- ----
 CAMELCASETABLEID                         NUMBER(38)
 CAMELCASEDESCRIPTION                     VARCHAR2(30)
 CAMELCASESTARTDATE                       DATE

Oracle is a fine database, but it is different from SQL Server, and both are different from mySQL. You cannot assume that you can write code one way that will work correctly for all DBMSs, with just some dialect translation. Things like NULL treatment, locking, concurrency are very different.

r937 · #3 (**permalink**) 04-06-06, 07:16

suppose you are in mysql, and have a table called mytable

guess what you get if you run this --

Code:

SELECT "Silly Column Name" FROM mytable

if you guys are planning to port the app to mysql and oracle, it is already well past time for you to be testing stuff like this for yourself

the person who designed the tables should be shot

Changhong · #4 (**permalink**) 04-06-06, 07:24

Thanks for your reply, it is very helpful.

I am particularly interested in last one, because I really like using PascalCasing, as I may use some code generation tool to generate my C# data access classes. The PascalCasing of database object names will result in better C# class and property names.

So on Oracle, can I quote my pascal cased names when I create a table as

SQL > create table MyPascalTable
(
“MyPascalCaseTableId” integer,
“MyPascalCaseName” varchar(50),
“MyPascalCaseDescription” varchar(255)
);

And so that they will be stored as case-sensitive.
And this should be only required once when creating the table, and we don’t have to quote the names when we write some queries, but just make sure all the letters are in correct case? (Sorry, I don’t have an Oracle available, so I can’t test it out myself)

If it works this way, is it considered to be a good practice, and won’t upset most of the Oracle DBAs too much?

Thanks

andrewst · #5 (**permalink**) 04-06-06, 09:57

No, in Oracle if you define your column names with mixed case and in quotes like “MyPascalCaseTableId” then you must always use the quotes when referencing the column:

Code:

SQL> create table MyPascalTable
  2  (
  3  "MyPascalCaseTableId" integer,
  4  "MyPascalCaseName" varchar(50),
  5  "MyPascalCaseDescription" varchar(255)
  6  );

Table created.

SQL> select MyPascalCaseTableId from MyPascalTable;
select MyPascalCaseTableId from MyPascalTable
       *
ERROR at line 1:
ORA-00904: "MYPASCALCASETABLEID": invalid identifier


SQL> select "MyPascalCaseTableId" from MyPascalTable;

no rows selected

And just for good measure:

Code:

SQL> select "MyPascalCaseTableId" from "MyPascalTable";
select "MyPascalCaseTableId" from "MyPascalTable"
                                  *
ERROR at line 1:
ORA-00942: table or view does not exist

(because we did not use quotes when creating the table, so its stored name is "MYPASCALTABLE" and not "MyPascalTable".)

Using mixed case names is considered to be poor practice and Oracle DBAs will hound you until the end of your days if you do this

Pat Phelan · #6 (**permalink**) 04-06-06, 10:54

You can certainly use cammel case names without using quotes in Microsoft SQL, Oracle, and MySQL. Oracle will cheerfully shift the unquoted CammelCase to upper case, MySQL and Microsoft will take the CammelCase verbatim. Oracle will later shift unquoted CammelCase in queries to upper case, and process them gleefully, and Microsoft will usually use a case insensitve collation for the system tables, so neither of them will care about upper, lower, or CammelCase. MySQL normally uses case sensitive collations for objects, so you'll have to consistently use upper, lower, or CammelCase, but you should be fine as long as you are consistent.

I suspect that your Data Modeler has set your project up for a relatively serious overhaul when it goes to QA, or worse yet after it "hits the streets" because (like Tony), I think that you'll get strong push back from the DBAs that try to implement it in its present state.

-PatP

r937 · #7 (**permalink**) 04-06-06, 11:09

pat, how mmany timmes did you mmean to mmention CammelCase with two emms?

oh, wait a mminute -- you're probably referring to bactrian CammelCase, which does have two hummps

mmy apologies

Pat Phelan · #8 (**permalink**) 04-06-06, 14:13

Roger Cammel does spell his name with two Ms. CammelCase wasn't corrupted to CamelCase until The Great Kahn introduced Turbo-Pascal in the 1980s.

-PatP

rajiravi · #9 (**permalink**) 04-06-06, 16:13

Pat, you piqued my curiosity. So I did a brief Google search and here is the Wikipedia reference http://en.wikipedia.org/wiki/CamelCase

I could not find any reference to Roger Cammel in it.

Ravi

Pat Phelan · #10 (**permalink**) 04-07-06, 00:43

Dr. Roger Cammel was a computer science professor at one of the big colleges out east somewhere. He was big into Simula, and a number of other languages. He was a major proponent of MixedCaseNaming a few aeons ago, in languages like Snobol, PL/1, Simula, Ratfor, etc.

He was often published in the mid 1970s in journals like the CACM (I don't remember if he was actually published in CACM or not, but journals like that). He was so tightly associated with the coding style that it became known as CammelCasing.

When Phillippe Kahn published the Turbo Pascal 1.0 manual, the convention became known as Camel Casing, with the explanation that the style resembled Camel humps. That rather annoyed me, but nobody asked.

I haven't thought about that for years, but this conversation brought it all back. It reminds me why I don't get that excited about coding styles etc any more... Its bad for your health!

-PatP

andrewst · #11 (**permalink**) 04-07-06, 05:11

Quote:

Originally Posted by Pat Phelan

Dr. Roger Cammel ...

I don't believe a word of it

Pat Phelan · #12 (**permalink**) 04-07-06, 09:24

I can't find any reference to Dr. Roger Cammel online, which kind of surprises me. Since I didn't know him, and I assume that he's dead by now, the best I could do is scour old journals to find his letters. That's a huge investment in time, with almost no payback. I'm not going there, thanks.

-PatP