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?
Now the identifiers are not case-sensitive, so any of the following will work:
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:
SQL> create table myCamelCaseTable
2 ( camelCaseTableId integer
3 , camelCaseDescription varchar2(30)
4 , camelCaseStartDate date
SQL> desc myCamelCaseTable
Name Null? Type
------------------------------- -------- ----
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.
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
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?
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.
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!
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.