Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2005
    Posts
    4

    White space in table names

    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

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2005
    Posts
    4
    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

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  9. #9
    Join Date
    Apr 2004
    Location
    Toronto, Canada
    Posts
    249

    Wikipedia Entry for Camel Case

    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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Quote Originally Posted by Pat Phelan
    Dr. Roger Cammel ...
    I don't believe a word of it

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Roger has a web site here: http://camelphotos.com/

    you can see him in the photo labelled "Roger and Gobi the camel take a short rest break"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    FYI:
    Quote Originally Posted by Pat Phelan
    MySQL normally uses case sensitive collations for objects
    By default, not on Windows (which I'm assuming is the environment).
    http://dev.mysql.com/doc/refman/5.0/...nsitivity.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •