RE: Database naming conventions

[Jesse, Rich]

Not "TICK" as in "Deer-" or "Wood-" (which look like corn kernels with legs
when they dig in and suck your blood), but as in "- vs.  The Uncommon Cold",
"- and Arthur", and "SPN!".

http://www.thetick.ws/

:)

Now back to naming new databases...

Rich


Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA


-Original Message-
Sent: Friday, February 14, 2003 4:39 PM
To: Multiple recipients of list ORACLE-L


Raj
 TICK does not stand for anything so interesting or pleasant in
Wisconsin.
   Sorry Rich, just a little upper midwestern humor.



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. Eden Prairie, Minnesota.
[EMAIL PROTECTED] 

-Original Message-
Sent: Friday, February 14, 2003 4:19 PM
To: Multiple recipients of list ORACLE-L



We do have a TICK ... it stands for sportsTICKer ... 

Raj 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[DENNIS WILLIAMS]

Raj
 TICK does not stand for anything so interesting or pleasant in
Wisconsin.
   Sorry Rich, just a little upper midwestern humor.



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. Eden Prairie, Minnesota.
[EMAIL PROTECTED] 

-Original Message-
Sent: Friday, February 14, 2003 4:19 PM
To: Multiple recipients of list ORACLE-L



We do have a TICK ... it stands for sportsTICKer ... 

Raj 
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art! 


-Original Message- 
 ] 
Sent: Friday, February 14, 2003 5:04 PM 
To: Multiple recipients of list ORACLE-L 


DUPLO, AMIGA, PLINK, CHEWY, HOPS, EDGE, ENCLAVE, and TICK. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[Jamadagni, Rajendra]

Title: RE: Database naming conventions





We do have a TICK ... it stands for sportsTICKer ...


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: Jesse, Rich [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 5:04 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Database naming conventions



DUPLO, AMIGA, PLINK, CHEWY, HOPS, EDGE, ENCLAVE, and TICK.



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

RE: Database naming conventions

[Godlewski, Melissa]

Title: RE: Database naming conventions



Thanks 
everyone for your opinions.  I believe I have some good examples of why not 
to use ora.
 
This 
list is great!

  -Original Message-From: Jamadagni, Rajendra 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, February 14, 2003 
  3:59 PMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: Database naming conventions
  We use following syntax ... 
  * FAM -- ABC Family Production 
  DB * OLDFAM  -- essentially FAM but as of 2AM 
  today (refreshed daily or on demand) * 
  FAMQA   -- FAM QA * FAMTEST -- FAM User 
  Acceptance * FAMDEV  -- Fam development 
  
  This works good for us ... 
  Raj __ Rajendra Jamadagni  
      MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any 
  opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
  QOTD: Any clod can have facts, but having an opinion 
  is an art! 
  --- "Godlewski, Melissa" <[EMAIL PROTECTED]> 
  wrote: > List, >  
  > I'm use to using a standard D=development T=test 
  P=production.  So > for a > database newly created on development it would be called 
  something > like > 
  D24X7.  Then when it was created on Production it would be called 
  > P24X7.  Or > along 
  similar lines. >  > 
  I'm working with an other DBA who wants everything to start with ora. 
  > Therefore it would be called orad24x7 and 
  orap24x7.  I've argued the > ora is 
  > rather redundant since everyone will know it's an Oracle 
  database > they are > 
  connecting to.  He is adamant it should have the ora 
  identification > so it is > easily identified.  I feel it will cause more confusion 
  having ora at > the > 
  beging of every dbname.  >  
  > Any thoughts for against either position? 
  >  > TIA > M.Godlewski >  

RE: Database naming conventions

[Jesse, Rich]

Now my left-brain is arguing with the right-brain.  Some of our test/dev DBs
are (or have been):

MULTI, ARENA, ORBIT, RALLY, and EMPIRE

(the word "playground" was too long...)  And I've created at one point or
another:

DUPLO, AMIGA, PLINK, CHEWY, HOPS, EDGE, ENCLAVE, and TICK.

No one's really able to figure out what they're used for by the name, but
then again it doesn't take that long to familiarize yourself with
Ford/Chevy/Mopar, Pepsi/Coke/RC, or Oracle/DB2/SQueaL_Server, either.  It's
no worse than trying to remember that "QTIERPP1" could be QTI's first
production ERP DB, but giving servers and DBs theme names is a whole lot
more fun.

Spon!  :)

Rich

Rich JesseSystem/Database Administrator
[EMAIL PROTECTED]   Quad/Tech International, Sussex, WI USA

-Original Message-
Sent: Friday, February 14, 2003 2:59 PM
To: Multiple recipients of list ORACLE-L


We use following syntax ... 
* FAM -- ABC Family Production DB 
* OLDFAM  -- essentially FAM but as of 2AM today (refreshed daily or on
demand) 
* FAMQA   -- FAM QA 
* FAMTEST -- FAM User Acceptance 
* FAMDEV  -- Fam development 
This works good for us ... 
Raj 
__ 
Rajendra Jamadagni  MIS, ESPN Inc. 
Rajendra dot Jamadagni at ESPN dot com 
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art! 


--- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote: 
> List, 
>  
> I'm use to using a standard D=development T=test P=production.  So 
> for a 
> database newly created on development it would be called something 
> like 
> D24X7.  Then when it was created on Production it would be called 
> P24X7.  Or 
> along similar lines. 
>  
> I'm working with an other DBA who wants everything to start with ora. 
> Therefore it would be called orad24x7 and orap24x7.  I've argued the 
> ora is 
> rather redundant since everyone will know it's an Oracle database 
> they are 
> connecting to.  He is adamant it should have the ora identification 
> so it is 
> easily identified.  I feel it will cause more confusion having ora at 
> the 
> beging of every dbname.  
>  
> Any thoughts for against either position? 
>  
> TIA 
> M.Godlewski 
>  
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[Jamadagni, Rajendra]

Title: RE: Database naming conventions





We use following syntax ...


* FAM -- ABC Family Production DB
* OLDFAM  -- essentially FAM but as of 2AM today (refreshed daily or on demand)
* FAMQA   -- FAM QA
* FAMTEST -- FAM User Acceptance
* FAMDEV  -- Fam development 


This works good for us ...


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



--- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote:
> List, 
>  
> I'm use to using a standard D=development T=test P=production.  So
> for a
> database newly created on development it would be called something
> like
> D24X7.  Then when it was created on Production it would be called
> P24X7.  Or
> along similar lines.
>  
> I'm working with an other DBA who wants everything to start with ora.
> Therefore it would be called orad24x7 and orap24x7.  I've argued the
> ora is
> rather redundant since everyone will know it's an Oracle database
> they are
> connecting to.  He is adamant it should have the ora identification
> so it is
> easily identified.  I feel it will cause more confusion having ora at
> the
> beging of every dbname.  
>  
> Any thoughts for against either position?
>  
> TIA
> M.Godlewski
>  



This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*2

Re: Database naming conventions

[Arup Nanda]

This reminds me something that happened to me about seven years ago. We had
a head honcho (read DBA manager) who just had a sweet slide from DB2 word to
Oracle and as one of the first things he suggested was to rename all the
tables to be prefixed by "USERTAB_" and all the views by "USERVIEW_", and
you got the idea. Naturally he also wanted all the apps to be recoded to
reflect the new tablenames (he had no idea what a synonym was or he just
didn't care). What was the point behind "USERTAB_" prefix? It's user created
tables, stupid, as opposed to system created ones!

"But you can't rename the system tables to prefix SYSTAB_.", your truly
suggested.

"Sure you can", came the reponse growled back.

Everyone in the room smiled sheepishly. Realizing that he may have made a
blunder, Mr Name Prefixer roared at me:

"And you will make sure that it happens. that's your goal of this year - to
change the app and change the table names."

Mercifully, he didn't stick around longer. Pressure from within forced him
out to his familiar DB2 world. Perhaps he listened to his call from the
wild, the sweet sweet world where he can prefix whatever names to tables he
wants.

No, I didn't ask him what his cat's name was.

Arup

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, February 14, 2003 2:08 PM


> Melissa,
>
> Ask him if he name all his tables "TAB...", java class "Java...",
> his pet "CAT..." or "DOG..." etc?
>
> Richard
>
> -Original Message-
> Sent: Friday, February 14, 2003 1:44 PM
> To: Multiple recipients of list ORACLE-L
>
>
> there is no reason to call the database "ora."
>
> I understand the reasoning behind and the desire for naming
> conventions.
>
> What happens when your shop decides to go with MySQL (as this list has
> been talking about)... will he want to rename the database to
> mysqlt24x7?  will he even be allowed to have a database name that long?
>
>
>
> --- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote:
> > List,
> >
> > I'm use to using a standard D=development T=test P=production.  So
> > for a
> > database newly created on development it would be called something
> > like
> > D24X7.  Then when it was created on Production it would be called
> > P24X7.  Or
> > along similar lines.
> >
> > I'm working with an other DBA who wants everything to start with ora.
> > Therefore it would be called orad24x7 and orap24x7.  I've argued the
> > ora is
> > rather redundant since everyone will know it's an Oracle database
> > they are
> > connecting to.  He is adamant it should have the ora identification
> > so it is
> > easily identified.  I feel it will cause more confusion having ora at
> > the
> > beging of every dbname.
> >
> > Any thoughts for against either position?
> >
> > TIA
> > M.Godlewski
> >
> >
> >
> >
> >
>
>
> __
> Do you Yahoo!?
> Yahoo! Shopping - Send Flowers for Valentine's Day
> http://shopping.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rachel Carmichael
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Richard Ji
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.ora

RE: Database naming conventions

[Richard Ji]

Melissa,

Ask him if he name all his tables "TAB...", java class "Java...",
his pet "CAT..." or "DOG..." etc?

Richard

-Original Message-
Sent: Friday, February 14, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L


there is no reason to call the database "ora."

I understand the reasoning behind and the desire for naming
conventions.

What happens when your shop decides to go with MySQL (as this list has
been talking about)... will he want to rename the database to
mysqlt24x7?  will he even be allowed to have a database name that long?



--- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote:
> List, 
>  
> I'm use to using a standard D=development T=test P=production.  So
> for a
> database newly created on development it would be called something
> like
> D24X7.  Then when it was created on Production it would be called
> P24X7.  Or
> along similar lines.
>  
> I'm working with an other DBA who wants everything to start with ora.
> Therefore it would be called orad24x7 and orap24x7.  I've argued the
> ora is
> rather redundant since everyone will know it's an Oracle database
> they are
> connecting to.  He is adamant it should have the ora identification
> so it is
> easily identified.  I feel it will cause more confusion having ora at
> the
> beging of every dbname.  
>  
> Any thoughts for against either position?
>  
> TIA
> M.Godlewski
>  
>  
>  
>  
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Richard Ji
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[Nick Wagner]

Title: RE: Database naming conventions





how about just an 'o'  


od24x7
op24x7


you could then use 'u' for udb and 'm' for MySQL, and 's' for SQL Server. 


-Original Message-
From: Rachel Carmichael [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 10:44 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Database naming conventions



there is no reason to call the database "ora."


I understand the reasoning behind and the desire for naming
conventions.


What happens when your shop decides to go with MySQL (as this list has
been talking about)... will he want to rename the database to
mysqlt24x7?  will he even be allowed to have a database name that long?




--- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote:
> List, 
>  
> I'm use to using a standard D=development T=test P=production.  So
> for a
> database newly created on development it would be called something
> like
> D24X7.  Then when it was created on Production it would be called
> P24X7.  Or
> along similar lines.
>  
> I'm working with an other DBA who wants everything to start with ora.
> Therefore it would be called orad24x7 and orap24x7.  I've argued the
> ora is
> rather redundant since everyone will know it's an Oracle database
> they are
> connecting to.  He is adamant it should have the ora identification
> so it is
> easily identified.  I feel it will cause more confusion having ora at
> the
> beging of every dbname.  
>  
> Any thoughts for against either position?
>  
> TIA
> M.Godlewski
>  
>  
>  
>  
> 



__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California    -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re:Database naming conventions

[dgoulet]

Melissa,

My naming convention is to have the host name and the database instance
follow it.  I have been using ORACLE_SID's that are numeric for years since v6
which helps therefore the 02 database on the server brahms.vicr.com is named
brahms2.  Makes life easier identifying where you are.  Having ora preclude it
is rather wierd and meaningless.

Dick Goulet

Reply Separator
Author: "Godlewski; Melissa" <[EMAIL PROTECTED]>
Date:   2/14/2003 8:23 AM

List, 
 
I'm use to using a standard D=development T=test P=production.  So for a
database newly created on development it would be called something like
D24X7.  Then when it was created on Production it would be called P24X7.  Or
along similar lines.
 
I'm working with an other DBA who wants everything to start with ora.
Therefore it would be called orad24x7 and orap24x7.  I've argued the ora is
rather redundant since everyone will know it's an Oracle database they are
connecting to.  He is adamant it should have the ora identification so it is
easily identified.  I feel it will cause more confusion having ora at the
beging of every dbname.  
 
Any thoughts for against either position?
 
TIA
M.Godlewski
 
 
 
 










List, 

 
I'm 
use to using a standard D=development T=test P=production.  So for a 
database newly created on development it would be called something like 
D24X7.  Then when it was created on Production it would be called 
P24X7.  Or along similar lines.
 
I'm 
working with an other DBA who wants everything to start with ora.  
Therefore it would be called orad24x7 and orap24x7.  I've argued the ora is

rather redundant since everyone will know it's an Oracle database they are 
connecting to.  He is adamant it should have the ora identification so it 
is easily identified.  I feel it will cause more confusion having ora at 
the beging of every dbname.  
 
Any 
thoughts for against either position?
 
TIA
M.Godlewski
 
 
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Database naming conventions

[Rachel Carmichael]

there is no reason to call the database "ora."

I understand the reasoning behind and the desire for naming
conventions.

What happens when your shop decides to go with MySQL (as this list has
been talking about)... will he want to rename the database to
mysqlt24x7?  will he even be allowed to have a database name that long?



--- "Godlewski, Melissa" <[EMAIL PROTECTED]> wrote:
> List, 
>  
> I'm use to using a standard D=development T=test P=production.  So
> for a
> database newly created on development it would be called something
> like
> D24X7.  Then when it was created on Production it would be called
> P24X7.  Or
> along similar lines.
>  
> I'm working with an other DBA who wants everything to start with ora.
> Therefore it would be called orad24x7 and orap24x7.  I've argued the
> ora is
> rather redundant since everyone will know it's an Oracle database
> they are
> connecting to.  He is adamant it should have the ora identification
> so it is
> easily identified.  I feel it will cause more confusion having ora at
> the
> beging of every dbname.  
>  
> Any thoughts for against either position?
>  
> TIA
> M.Godlewski
>  
>  
>  
>  
> 


__
Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day
http://shopping.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[Godlewski, Melissa]

Title: RE: Database naming conventions





Thanks,   I Talking about the SID. 


I got my smile for the day, putting version etc. in would certainly add to the absurdity.


-Original Message-
From: Jay Hostetter [mailto:[EMAIL PROTECTED]]
Sent: Friday, February 14, 2003 11:59 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Database naming conventions



Are we talking SID or connect string?  I seem to remember that there was (or is on certain OSs) a limit to the length of the SID - so I tend to keep the SIDs short and sweet.  If it is the connect string - then who is it that needs to know it is an Oracle database?  The user?  Why?  The DBA?  Er...shouldn't he/she already know that?  

I agree with you - I would eliminate the 'ora'.  You can illustrate the absurdity by insisting that it also include the version number and OS ora9201tru64p24x7.



Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA  USA


>>> [EMAIL PROTECTED] 02/14/03 11:23AM >>>
List, 
 
I'm use to using a standard D=development T=test P=production.  So for a
database newly created on development it would be called something like
D24X7.  Then when it was created on Production it would be called P24X7.  Or
along similar lines.
 
I'm working with an other DBA who wants everything to start with ora.
Therefore it would be called orad24x7 and orap24x7.  I've argued the ora is
rather redundant since everyone will know it's an Oracle database they are
connecting to.  He is adamant it should have the ora identification so it is
easily identified.  I feel it will cause more confusion having ora at the
beging of every dbname.  
 
Any thoughts for against either position?
 
TIA
M.Godlewski
 
 
 
 




**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California    -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[DENNIS WILLIAMS]

Melissa - You didn't mention which system you are on. My comments apply more
to Unix.
   Oracle warns not to make the instance name too long. Formerly they
recommended you keep it to 4 characters. Not sure how that would apply
today. It does make your processes long.
My #1 objective is to make sure nothing gets bollixed up in production,
most particularly by me. Many DBA tasks are quite capable of creating a
production disaster. I like the idea of putting "T" for test or whatever at
the FRONT so that is just one small thing that might possibly save my bacon.
Burying it in the middle obscures that.
The only reason I can think of putting the "ora" at the front is for the
convenience of the sys admins, but Oracle puts that on the front anyway, at
least on my Unix systems.



Dennis Williams 
DBA, 40%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

 

-Original Message-
Sent: Friday, February 14, 2003 10:24 AM
To: Multiple recipients of list ORACLE-L


List, 
 
I'm use to using a standard D=development T=test P=production.  So for a
database newly created on development it would be called something like
D24X7.  Then when it was created on Production it would be called P24X7.  Or
along similar lines.
 
I'm working with an other DBA who wants everything to start with ora.
Therefore it would be called orad24x7 and orap24x7.  I've argued the ora is
rather redundant since everyone will know it's an Oracle database they are
connecting to.  He is adamant it should have the ora identification so it is
easily identified.  I feel it will cause more confusion having ora at the
beging of every dbname.  
 
Any thoughts for against either position?
 
TIA
M.Godlewski
 
 
 
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Database naming conventions

[Tony Johnson]

I had 
the same arguments with a consultant we had in at one time I compromised with 
him. The database files are in a directory called 'oradata' and we use 
a
designator of D development, T for Testing, B for Beta, P for Production 
and S for Stress Test.

  -Original Message-From: Godlewski, Melissa 
  [mailto:[EMAIL PROTECTED]]Sent: Friday, February 14, 
  2003 9:24 AMTo: Multiple recipients of list 
  ORACLE-LSubject: Database naming conventions
  List, 
   
  I'm 
  use to using a standard D=development T=test P=production.  So for a 
  database newly created on development it would be called something like 
  D24X7.  Then when it was created on Production it would be called 
  P24X7.  Or along similar lines.
   
  I'm 
  working with an other DBA who wants everything to start with ora.  
  Therefore it would be called orad24x7 and orap24x7.  I've argued the ora 
  is rather redundant since everyone will know it's an Oracle database they are 
  connecting to.  He is adamant it should have the ora identification so it 
  is easily identified.  I feel it will cause more confusion having ora at 
  the beging of every dbname.  
   
  Any 
  thoughts for against either position?
   
  TIA
  M.Godlewski
   
   
   
   

Re: Database naming conventions

[Jay Hostetter]

Are we talking SID or connect string?  I seem to remember that there was (or is on 
certain OSs) a limit to the length of the SID - so I tend to keep the SIDs short and 
sweet.  If it is the connect string - then who is it that needs to know it is an 
Oracle database?  The user?  Why?  The DBA?  Er...shouldn't he/she already know that?  
I agree with you - I would eliminate the 'ora'.  You can illustrate the absurdity by 
insisting that it also include the version number and OS ora9201tru64p24x7.



Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA  USA

>>> [EMAIL PROTECTED] 02/14/03 11:23AM >>>
List, 
 
I'm use to using a standard D=development T=test P=production.  So for a
database newly created on development it would be called something like
D24X7.  Then when it was created on Production it would be called P24X7.  Or
along similar lines.
 
I'm working with an other DBA who wants everything to start with ora.
Therefore it would be called orad24x7 and orap24x7.  I've argued the ora is
rather redundant since everyone will know it's an Oracle database they are
connecting to.  He is adamant it should have the ora identification so it is
easily identified.  I feel it will cause more confusion having ora at the
beging of every dbname.  
 
Any thoughts for against either position?
 
TIA
M.Godlewski
 
 
 
 



**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jay Hostetter
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Database naming conventions

[Godlewski, Melissa]

List, 

 
I'm 
use to using a standard D=development T=test P=production.  So for a 
database newly created on development it would be called something like 
D24X7.  Then when it was created on Production it would be called 
P24X7.  Or along similar lines.
 
I'm 
working with an other DBA who wants everything to start with ora.  
Therefore it would be called orad24x7 and orap24x7.  I've argued the ora is 
rather redundant since everyone will know it's an Oracle database they are 
connecting to.  He is adamant it should have the ora identification so it 
is easily identified.  I feel it will cause more confusion having ora at 
the beging of every dbname.  
 
Any 
thoughts for against either position?
 
TIA
M.Godlewski
 
 
 
 

Re: Naming Conventions....

[Stephane Faroult]

> Shibu MB wrote:
> 
> Hi  all...
> 
>  What are the naming conventions u guys follow  when
> designing a database ???.Can  anybody send me a general document on
> this. I am trying to make   the attribute name unique in my database
> but i dunno  what naming convention i   have to follow for this
> ..   I have tried all kinda combinations  but some howthe
> names are not coming unique and  because of this the developers may
> get confused  know ...  so  please tell me what  naming convention
> are followed by u DBA s
> 
> 
> 
> Thanks
> Shibu
> 
> 
> 

Shibu,

There are probably as many naming conventions as DBAs ... What
matters is consistency.
You may find the following URL useful :
  http://www.oriole.com/frameindexST.html

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Naming Conventions....

[Shibu MB]

Hi  
all...
 
 
What are the naming conventions u guys follow  when designing a database 
???.Can  anybody send me a general document on this. I am trying to 
make   the attribute name unique in my database  but i 
dunno  what naming convention i   have to follow for this 
..   I have tried all kinda combinations  but some 
how    the names are not coming unique and  because of this  
the developers may get confused  know ...  so  please tell me 
what  naming convention  are followed by u DBA s 
 
 
 
Thanks
Shibu
 
 
 

role naming conventions

[Ray Stell]

Oracle seems to be inconsistant in the way it names
roles: 

SQL> select role, GRANTED_ROLE from ROLE_ROLE_PRIVS;

ROLE   GRANTED_ROLE
-- --
DBADELETE_CATALOG_ROLE
DBAEXECUTE_CATALOG_ROLE
DBAEXP_FULL_DATABASE
DBAIMP_FULL_DATABASE
DBASELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE   HS_ADMIN_ROLE
EXP_FULL_DATABASE  EXECUTE_CATALOG_ROLE
EXP_FULL_DATABASE  SELECT_CATALOG_ROLE
IMP_FULL_DATABASE  EXECUTE_CATALOG_ROLE
IMP_FULL_DATABASE  SELECT_CATALOG_ROLE
OEM_MONITORCONNECT


Sometimes it's _ROLE and sometimes it not.  Any strong 
recommendataion for local names of roles?  Is there a good
guide to naming in general?   Thanks.
===
Ray Stell   [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[sundeep maini]

If you attend a database class in school they will tell you table
names are always plural. Each row in a table represents one instance
of an entiry e.g. each row in Employees table represents (attributes
of) one single employee. A table represents all rows/instances of the
entiy (employee) collectively and hence should be plural.

>From One of our naming std documents.

Entity models a thing of substance to business and its name must be
the same as the business counterpart it represents e.g. employee,
department, lab_value etc. Any smart scheme (add needless suffixes
like _t or _master or use of abbreviations (when you haven't hit 30
char name limit) will only deviate you from the prime business
term/concept. 

Entity attributes are properties of the business entity being modeled
and also must follow the business terms used to describe those
properties.  

On use of Designer

Tables and columns are a physical model rendering of the logical
model and a tool like Designer will automate the derivation of the
physical model from the logical model. In a way you are deriving
names of the schema artifacts from the logical model. That pegs all
the different manifestations of a business entity (entity, table,
object) to one source(logical model). That should make it easier to
maintain consistency across models by using business terms as a root
for representing a concept. If you modify the root you can rerun the
derivation process and percolate the changes downstream.

Gaps in mapping Business World to Physical Model...

In the physical model you may have additional attributes associated
with the entity which do not have a mapping to the physical world
e.g. surrogate keys (a.k.a aritificial entity identifiers). Designer
stores 3 names for an entity, an entity name (should be the business
entity being modeled), plural name (used to name tables generated
from the transformation of the entity), and the entity short name
(used as a prefix of the physical model artifacts which do not map to
business entity/attributes directly (e.g. generated primary keys are
_pk and surrogate keys are _id etc.). Since
this tool encapsultes prebuilt name derivation rules, you'll benefit
from using it rather than having to debate how to name such
(non-mapping) artifacts.

Use of domains is another force mutiplier. It provides a place to
docuement a business term once and then reuse its definition at
various places (as definition of attributes/columns).




--- kkennedy <[EMAIL PROTECTED]> wrote:
> Just as a style issue, I like the distinction made by Oracle Case,
> I mean Designer 2000, or is that Designer 6i, or is it Designer 9i
> (I'm so confused).  In the CASE*Method, entity names are singular
> in the logical model and become plural when transformed into table
> names in the physical model.
> 
> In my current shop, table names are singular -- I could tell right
> away that they didn't use Designer.  I don't think it matters much
> except that consistency is nice.
> 
> Kevin Kennedy
> First Point Energy Corporation
> 
> If you take RAC out of Oracle you get OLE!  What can this mean?
> 
> -Original Message-
> Sent: Wednesday, July 31, 2002 7:49 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Many thanks to all for your feedback and advice! As I suspected,
> naming
> conventions are really a matter of someone's personal preference,
> and
> what's really important is to keep it standard and consistent.
> 
> One [hopefully] last question:  What's the consensus (if there is
> such a
> thing) on plural vs. singular table names?
> 
> Gary Chambers
> 
> //-
> // Lucent Technologies GIO/Unix
> // 4 Robbins Road, Westford, MA 01886
> // 978-399-0481 / 888-480-6924 (Pager)
> // Nothing fancy and nothing Microsoft
> //-
> 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Gary Chambers
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
>

> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like
> subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: kkennedy
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-505

RE: Table Naming Conventions

[Rachel Carmichael]

I concede your point

btw -- my 12 year old car has the combination lap/shoulder belt :)


--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Assuming reliability in something that's inherently unreliable
> creates
> danger. The experienced learn to ignore naming standards, as you
> describe below. The inexperienced--the very people that the standards
> were ostensibly designed to help--are tricked by them.
> 
> My point is that naming standards that embed un-enforced information
> end
> up being more of an unintentional dirty trick than an aid. It's kind
> of
> like those old cars with the automatic shoulder restraint, but you
> had
> to clip the lap belt yourself. It was no less effort to clip the lap
> belt than to clip a shouder/lap belt combination. If you wore the
> shoulder belt but not the lap belt, you were actually more likely to
> die
> in an accident than if you wore neither. The only things the
> automatic
> lap belt accomplished were:
> 
> * Made the car more expensive to produce and purchase
> * Annoyed experienced drivers because it was slow
> * Annoyed experienced drivers because it often hit you on the head
> * Killed more inexperienced drivers
> * Allowed your Congressman to appear that he cared for your safety
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Wednesday, July 31, 2002 6:13 AM
> To: Multiple recipients of list ORACLE-L
> 
> Cary,
> 
> As a DBA, I tend not to rely on names anyway because I believe that
> documentation etc is out of date and incorrect two seconds after it
> is
> completed.
> 
> But for my developers, it does help to have some sort of convention
> when they read explain plans, especially if I also impose the rule
> that
> no one can create anything except me. 
> 
> And then there is the unanswerable argument of "corporate policy
> dictates we have naming standards" :)
> 
> Rachel
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > Rachel, one of the SQL statements in our Clinic that people find
> the
> > hardest to optimize is one that has a thing that looks like
> > "id_number =
> > 1" in the where clause. "id_number" is the table's primary key,
> > yet
> > the query spends 20 seconds executing a full-table scan. Any
> guesses?
> > 
> > It's because "id_number" was actually defined as a varchar2 column.
> > Oracle's implicit type coercion converts the predicate into
> > "to_number(id_number) = 1". Presto: the PK index is useless.
> > 
> > This and dozens of other unnecessarily pathological problems await
> > people who try to embed too much information into their names. 
> > 
> > 
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > 
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > Dallas
> > 
> > 
> > 
> > -Original Message-
> > Carmichael
> > Sent: Tuesday, July 30, 2002 8:09 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > I can see your point, In the data warehouse we are building here,
> the
> > modeler is planning on prefixing tables with the type of table (D_
> > for
> > dimension tables, F_ for fact, etc)
> > 
> > Hm, you mean we have to go back and revisit the naming standards
> that
> > they developed? Can I please suffix the column names with an
> > indicator
> > of the datatype? :)
> > 
> > The biggest problem is that most management wants "naming
> > standards"...
> > 
> > 
> > Rachel
> > 
> > 
> > --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > > I just think it's a waste. You can tell by context what kind of
> > thing
> > > a
> > > thing is. For example, consider: "select a.flarg from bloing a
> > where
> > > a.croopoo > 7". This can be understood by syntactical context
> (even
> > > with
> > > the nonsense names), without having to rename "bloing" to
> > > "bloing_table".
> > > 
> > > Most of the embedding of type names into object names that I've
> >

Re: Table Naming Conventions

[John Thomas]

Singular. 

On the grounds that almost every table is a collection of
things/contains multiple rows. So the "S" would be superfluous, except
for things like SHEEP. 

Cheers, 

John Thomas 
Oracle development DBA

In article <[EMAIL PROTECTED]>, Gary Chambers
<[EMAIL PROTECTED]> writes
>Many thanks to all for your feedback and advice! As I suspected, naming
>conventions are really a matter of someone's personal preference, and
>what's really important is to keep it standard and consistent.
>
>One [hopefully] last question:  What's the consensus (if there is such a
>thing) on plural vs. singular table names?
>
>Gary Chambers
>
>//-
>// Lucent Technologies GIO/Unix
>// 4 Robbins Road, Westford, MA 01886
>// 978-399-0481 / 888-480-6924 (Pager)
>// Nothing fancy and nothing Microsoft
>//-
>
>
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 
John Thomas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Thomas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Jared . Still]

Kevin,

P_EMPLOYEE doesn't tell you that it's an index, it tells you that this 
index
is used to support a primary key.  big difference.  :)

I like to use something similar when naming indexes and constraints, but
prefer suffixes to prefixes.

EMPLOYEE_P is easier to 'group by' in a report.

Jared






"kkennedy" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/30/2002 06:08 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
    cc: 
Subject:RE: Table Naming Conventions


Hmmm.  Seems I have to play devil's advocate on this one.  So, you see no 
purpose to index names such as:

P_EMPLOYEE for a Primary key on the employee table
or UM_METER_CODE for a unique index on the meter table meter_code column
or FE_DEPT for a foreign key index on the employee table dept column
or IIE_LOG_DATE for a non-unique index on the import_export table log_date 
column?

The first letter doesn't help me figure out that the object is an index, 
but it sure helps to show what the index is being used for.

Constraint naming is another area where we use a one character prefix to 
identify the constraint type, in part to conform to index naming.

The keeper of standards here (not me) has also imposed prefix conventions 
on global temporary tables and on selected classes of tables such as work 
tables or external cross reference tables.  It does help keep the 
developers in line.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE!  What can this mean?


-Original Message-
Sent: Tuesday, July 30, 2002 4:58 PM
To: Multiple recipients of list ORACLE-L


You already know it's an index, why would you include that as part of the 
name?

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/30/2002 02:29 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
    cc: 
Subject:RE: Table Naming Conventions


My supervisor/client wants object types in names - except tables like I_ 
for indexes.  Why do you say stay away from this?
-Original Message- 
Sent: Tuesday, July 30, 2002 5:10 PM 
To: Multiple recipients of list ORACLE-L 

Here's a start. Not a checklist by any means, just kind of a micro pet 
peeves list. 
* Decide today whether table names will be singular or plural. Do you 
want a THING (singular) table? Or a THINGS (plural) table? 
* Don't use case-sensitive names. E.g., use THING (without quotes) in 
your CREATE (DDL) statement, which can be spelled "THING", "thing", 
"Thing", or even "tHiNG" in your developers' SQL. But don't make 
developers type stuff like this... 
select "Name" from "Thing" where "Id" = y  /* won't work without "" 
*/ 
* Don't embed the object type in the object's name. I used to see this 
all the time with tablespaces called XYZ_TS, indexes called IND_THING, 
and so on. 
* Decide today whether you want to abbreviate or not. If you do, then 
construct a formal, standard, consistent list of accepted abbreviations. 
Don't name one table CUSTOMER_THING and another CUST_HISTORY. 

Cary Millsap 
Hotsos Enterprises, Ltd. 
http://www.hotsos.com 
Upcoming events: 
- NCOAUG Training Day, Aug 16 Chicago 
- Miracle Database Forum, Sep 20-22 Middlefart Denmark 
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas 


-----Original Message- 
Chambers 
Sent: Tuesday, July 30, 2002 3:37 PM 
To: Multiple recipients of list ORACLE-L 
All... 
Will some of you please provide some insight on your table naming 
conventions?  I'm in the very early planning stages of what will likely 
be a large and complex schema (IT asset inventory).  I have a chance to 
start it correctly.  TIA 
Gary Chambers 
//- 
// Lucent Technologies GIO/Unix 
// 4 Robbins Road, Westford, MA 01886 
// 978-399-0481 / 888-480-6924 (Pager) 
// Nothing fancy and nothing Microsoft 
//- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Gary Chambers 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California-- Public Internet access / Mailing Lists 
 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Cary Millsap 
  INET: [EMAIL PROTECTED] 
Fat City Networ

Re: Table Naming Conventions

[Jared . Still]

In Entity Relationship modeling, the entity names are singular.  This is 
because
an entity refers to a single instance of the object described.

Tables should be plural, as they refer to many instances of each object.

At least that works for me.

Jared






Gary Chambers <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/31/2002 07:48 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:    Table Naming Conventions


Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[kkennedy]

Just as a style issue, I like the distinction made by Oracle Case, I mean Designer 
2000, or is that Designer 6i, or is it Designer 9i (I'm so confused).  In the 
CASE*Method, entity names are singular in the logical model and become plural when 
transformed into table names in the physical model.

In my current shop, table names are singular -- I could tell right away that they 
didn't use Designer.  I don't think it matters much except that consistency is nice.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE!  What can this mean?

-Original Message-
Sent: Wednesday, July 31, 2002 7:49 AM
To: Multiple recipients of list ORACLE-L


Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: kkennedy
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[kkennedy]

I agree that if a naming standard is not enforced, it can be worse than useless.

When people rely on things that cannot be trusted in my shop, it means that I'm not 
doing my job properly -- provided those things are within my realm of influence.

On the other hand, I have been a contractor/consultant during most of my DBA career.  
Consultants have little or no control over the environment they work in.  In that 
environment, your attitude is fully justified.  A consultant cannot afford any 
assumptions when walking into a new environment.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE!  What can this mean?


-Original Message-
Sent: Tuesday, July 30, 2002 7:03 PM
To: Multiple recipients of list ORACLE-L


I don't like such prefixes because they make it easier to lie or make
mistakes. I've seen people try for hours to figure out why something
doesn't make sense, only to find out that the index whose name has a 'U'
in it actually wasn't created with the "UNIQUE" attribute.

Names falsely cause reliance that cannot be trusted. Making reports on
such things easier to run is, to my mind, a better idea than integrating
too much information into naming standards.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Sent: Tuesday, July 30, 2002 8:09 PM
To: Multiple recipients of list ORACLE-L

Hmmm.  Seems I have to play devil's advocate on this one.  So, you see
no purpose to index names such as:

P_EMPLOYEE for a Primary key on the employee table
or UM_METER_CODE for a unique index on the meter table meter_code column
or FE_DEPT for a foreign key index on the employee table dept column
or IIE_LOG_DATE for a non-unique index on the import_export table
log_date column?

The first letter doesn't help me figure out that the object is an index,
but it sure helps to show what the index is being used for.

Constraint naming is another area where we use a one character prefix to
identify the constraint type, in part to conform to index naming.

The keeper of standards here (not me) has also imposed prefix
conventions on global temporary tables and on selected classes of tables
such as work tables or external cross reference tables.  It does help
keep the developers in line.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE!  What can this mean?


-Original Message-
Sent: Tuesday, July 30, 2002 4:58 PM
To: Multiple recipients of list ORACLE-L


You already know it's an index, why would you include that as part of
the 
name?

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/30/2002 02:29 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc: 
Subject:RE: Table Naming Conventions


My supervisor/client wants object types in names - except tables like I_

for indexes.  Why do you say stay away from this?
-Original Message- 
Sent: Tuesday, July 30, 2002 5:10 PM 
To: Multiple recipients of list ORACLE-L 

Here's a start. Not a checklist by any means, just kind of a micro pet 
peeves list. 
* Decide today whether table names will be singular or plural. Do you 
want a THING (singular) table? Or a THINGS (plural) table? 
* Don't use case-sensitive names. E.g., use THING (without quotes) in 
your CREATE (DDL) statement, which can be spelled "THING", "thing", 
"Thing", or even "tHiNG" in your developers' SQL. But don't make 
developers type stuff like this... 
select "Name" from "Thing" where "Id" = y  /* won't work without "" 
*/ 
* Don't embed the object type in the object's name. I used to see this 
all the time with tablespaces called XYZ_TS, indexes called IND_THING, 
and so on. 
* Decide today whether you want to abbreviate or not. If you do, then 
construct a formal, standard, consistent list of accepted abbreviations.

Don't name one table CUSTOMER_THING and another CUST_HISTORY. 

Cary Millsap 
Hotsos Enterprises, Ltd. 
http://www.hotsos.com 
Upcoming events: 
- NCOAUG Training Day, Aug 16 Chicago 
- Miracle Database Forum, Sep 20-22 Middlefart Denmark 
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas 


-Original Message- 
Chambers 
Sent: Tuesday, July 30, 2002 3:37 PM 
To: Multiple recipients of list ORACLE-L 
All... 
Will some of you please provide some insight on your table naming 
conventions?  I'm in the very early planning stages of what will likely 
be a large and complex schema (IT asset inventory).  I have a chance to 
start it correctly.  TIA 
Gary Chambers 

RE: Table Naming Conventions

[Karniotis, Stephen]

Hmmm.  Proper data modeling dictates that all names be singular.  Plural
names imply that multiple values are stored.  That's a no no.

Thank You

Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct: (248) 865-4350
Mobile: (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com

 -Original Message-
Sent:   Wednesday, July 31, 2002 12:18 PM
To: Multiple recipients of list ORACLE-L
Subject:RE: Table Naming Conventions

Gary,  you said:

"What's the consensus (if there is such a
thing) on plural vs. singular table names?"

the correct answer is - BE CONSISTENT!!!

doesn't really matter (at least to me!)  :)

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, July 31, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L


Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it. 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Karniotis, Stephen
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[MacGregor, Ian A.]

Any Oracle Desinger user can tell you, entity names are singular, table names are 
plural.  

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

-Original Message-
Sent: Wednesday, July 31, 2002 9:18 AM
To: Multiple recipients of list ORACLE-L


I think the relational theory guys will tell you "singular." Which, I
think, is a good idea.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Chambers
Sent: Wednesday, July 31, 2002 9:49 AM
To: Multiple recipients of list ORACLE-L

Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[MacGregor, Ian A.]

I'm confused here.  Are you saying that 'date_column' will be converted to varchar2 or 
that it was taught  that the column would be converted?

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]

-Original Message-
Sent: Tuesday, July 30, 2002 9:53 PM
To: Multiple recipients of list ORACLE-L


That's a classic one, taught in various courses ever since version 5.
The most famous example was

select...
where date_column='12-31-99'

where date_column would be implicitly converted to varchar2. A little 
'explain
plan' effort  and all the confusion is easily avoided.




On 2002.07.31 00:08 Cary Millsap wrote:
> Rachel, one of the SQL statements in our Clinic that people find the
> hardest to optimize is one that has a thing that looks like "id_number
> =
> 1" in the where clause. "id_number" is the table's primary key,
> yet
> the query spends 20 seconds executing a full-table scan. Any guesses?
> 
> It's because "id_number" was actually defined as a varchar2 column.
> Oracle's implicit type coercion converts the predicate into
> "to_number(id_number) = 1". Presto: the PK index is useless.
> 
> This and dozens of other unnecessarily pathological problems await
> people who try to embed too much information into their names.
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Tuesday, July 30, 2002 8:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> I can see your point, In the data warehouse we are building here, the
> modeler is planning on prefixing tables with the type of table (D_ for
> dimension tables, F_ for fact, etc)
> 
> Hm, you mean we have to go back and revisit the naming standards that
> they developed? Can I please suffix the column names with an indicator
> of the datatype? :)
> 
> The biggest problem is that most management wants "naming
> standards"...
> 
> 
> Rachel
> 
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > I just think it's a waste. You can tell by context what kind of
> thing
> > a
> > thing is. For example, consider: "select a.flarg from bloing a where
> > a.croopoo > 7". This can be understood by syntactical context (even
> > with
> > the nonsense names), without having to rename "bloing" to
> > "bloing_table".
> >
> > Most of the embedding of type names into object names that I've seen
> > has
> > been implemented by users who were inexperienced at the time they
> > created the standard. They were worried that without embedding the
> > type
> > name into the object name, they might forget what kind of object it
> > was.
> > ...Most such naming conventions become onerous over time, long after
> > you
> > find out that you can find the type of something in the data
> > dictionary,
> > but after it's too late to save the thousands of extra characters of
> > typing that'll waste people's lifespans over time.
> >
> > In my old OFA paper, I made a joke about how we don't embed type
> > names
> > into object names in daily life, with just a few exceptions (Billy
> > the
> > Kid, Winnie the Pooh, Atilla the Hun, and the younger family members
> > of
> > the old Walton Family TV show are a few examples). If you have both
> a
> > dog and a child named "Rex," though, it's probably a good idea to
> > expect
> > them both to come when you call. With SQL, though, I can't think of
> a
> > case in which it's not easy to tell by syntactic context what kind
> of
> > thing you're talking about...
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > Dallas
> >
> >
> >
> > -Original Message-
> > Carmichael
> > Sent: Tuesday, July 30, 2002 4:49 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > Cary,
> >
> > you said
> > "* Don't embed the object type in the object's name. I used to see
> > this
> > all the time with tablespaces called XYZ

RE: Table Naming Conventions

[Cary Millsap]

I agree wholeheartedly with your second point. In theory, there should
be a table called "DISTRIBUTION" or something like that. Then,

(Customizations) = (Everything there is) - (DISTRIBUTION)

But in the absence of such a device, a naming convention will help.

(By the way, I think the term "naming convention" is more accurate than
"naming standard." The word "standard" somehow implies that a naming
rule is something more than voluntary. Organizationally, it may be
mandatory, but the computer doesn't care, which makes anyone's naming
decision--ultimately--voluntary.)


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Rich
Sent: Wednesday, July 31, 2002 10:09 AM
To: Multiple recipients of list ORACLE-L

Excellent point, Cary.  I have seen a table with all 7 of it's index
names
suffixed with "_PK".  Makes the monikor a bit useless, doesn't it?  I
was
about to kindly chide the dev who created them, but I couldn't come up
with
a sane reason why we should spend all that time renaming indexes, when
an
outer join to a %_CONSTRAINTS view will tell the whole story.

I do, however, enforce a rule that says that any object we create in the
ERP
app schema of our 3rd-party ERP DB be prefixed with "QT" or "QT_".  This
way, if we ever upgrade the app, I can very easily identify our custom
tables, indexes, triggers, etc for recreation.  Likewise, all the other
schemas we add to this DB are prefixed as well.

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI
USA


> -Original Message-
> From: Cary Millsap [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 30, 2002 9:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Table Naming Conventions
> 
> 
> I don't like such prefixes because they make it easier to lie or make
> mistakes. I've seen people try for hours to figure out why something
> doesn't make sense, only to find out that the index whose 
> name has a 'U'
> in it actually wasn't created with the "UNIQUE" attribute.
> 
> Names falsely cause reliance that cannot be trusted. Making reports on
> such things easier to run is, to my mind, a better idea than 
> integrating
> too much information into naming standards.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Mercadante, Thomas F]

Gary,  you said:

"What's the consensus (if there is such a
thing) on plural vs. singular table names?"

the correct answer is - BE CONSISTENT!!!

doesn't really matter (at least to me!)  :)

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, July 31, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L


Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Cary Millsap]

I think the relational theory guys will tell you "singular." Which, I
think, is a good idea.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Chambers
Sent: Wednesday, July 31, 2002 9:49 AM
To: Multiple recipients of list ORACLE-L

Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Jesse, Rich]

And NEVER EVER name an object to a keyword.  You wouldn't believe the hassle
w/3rd party tools we have because of a table called "FUNCTION"...

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA

> -Original Message-
> From: Cary Millsap [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 30, 2002 5:54 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Table Naming Conventions
> 
> 
> I just think it's a waste. You can tell by context what kind 
> of thing a
> thing is. For example, consider: "select a.flarg from bloing a where
> a.croopoo > 7". This can be understood by syntactical context 
> (even with
> the nonsense names), without having to rename "bloing" to
> "bloing_table".
> 
> Most of the embedding of type names into object names that 
> I've seen has
> been implemented by users who were inexperienced at the time they
> created the standard. They were worried that without 
> embedding the type
> name into the object name, they might forget what kind of 
> object it was.
> ...Most such naming conventions become onerous over time, 
> long after you
> find out that you can find the type of something in the data 
> dictionary,
> but after it's too late to save the thousands of extra characters of
> typing that'll waste people's lifespans over time.
> 
> In my old OFA paper, I made a joke about how we don't embed type names
> into object names in daily life, with just a few exceptions (Billy the
> Kid, Winnie the Pooh, Atilla the Hun, and the younger family 
> members of
> the old Walton Family TV show are a few examples). If you have both a
> dog and a child named "Rex," though, it's probably a good 
> idea to expect
> them both to come when you call. With SQL, though, I can't think of a
> case in which it's not easy to tell by syntactic context what kind of
> thing you're talking about...
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Jesse, Rich]

Excellent point, Cary.  I have seen a table with all 7 of it's index names
suffixed with "_PK".  Makes the monikor a bit useless, doesn't it?  I was
about to kindly chide the dev who created them, but I couldn't come up with
a sane reason why we should spend all that time renaming indexes, when an
outer join to a %_CONSTRAINTS view will tell the whole story.

I do, however, enforce a rule that says that any object we create in the ERP
app schema of our 3rd-party ERP DB be prefixed with "QT" or "QT_".  This
way, if we ever upgrade the app, I can very easily identify our custom
tables, indexes, triggers, etc for recreation.  Likewise, all the other
schemas we add to this DB are prefixed as well.

Rich Jesse   System/Database Administrator
[EMAIL PROTECTED]  Quad/Tech International, Sussex, WI USA


> -Original Message-
> From: Cary Millsap [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, July 30, 2002 9:03 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Table Naming Conventions
> 
> 
> I don't like such prefixes because they make it easier to lie or make
> mistakes. I've seen people try for hours to figure out why something
> doesn't make sense, only to find out that the index whose 
> name has a 'U'
> in it actually wasn't created with the "UNIQUE" attribute.
> 
> Names falsely cause reliance that cannot be trusted. Making reports on
> such things easier to run is, to my mind, a better idea than 
> integrating
> too much information into naming standards.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Table Naming Conventions

[Gary Chambers]

Many thanks to all for your feedback and advice! As I suspected, naming
conventions are really a matter of someone's personal preference, and
what's really important is to keep it standard and consistent.

One [hopefully] last question:  What's the consensus (if there is such a
thing) on plural vs. singular table names?

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Cary Millsap]

Assuming reliability in something that's inherently unreliable creates
danger. The experienced learn to ignore naming standards, as you
describe below. The inexperienced--the very people that the standards
were ostensibly designed to help--are tricked by them.

My point is that naming standards that embed un-enforced information end
up being more of an unintentional dirty trick than an aid. It's kind of
like those old cars with the automatic shoulder restraint, but you had
to clip the lap belt yourself. It was no less effort to clip the lap
belt than to clip a shouder/lap belt combination. If you wore the
shoulder belt but not the lap belt, you were actually more likely to die
in an accident than if you wore neither. The only things the automatic
lap belt accomplished were:

* Made the car more expensive to produce and purchase
* Annoyed experienced drivers because it was slow
* Annoyed experienced drivers because it often hit you on the head
* Killed more inexperienced drivers
* Allowed your Congressman to appear that he cared for your safety


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Carmichael
Sent: Wednesday, July 31, 2002 6:13 AM
To: Multiple recipients of list ORACLE-L

Cary,

As a DBA, I tend not to rely on names anyway because I believe that
documentation etc is out of date and incorrect two seconds after it is
completed.

But for my developers, it does help to have some sort of convention
when they read explain plans, especially if I also impose the rule that
no one can create anything except me. 

And then there is the unanswerable argument of "corporate policy
dictates we have naming standards" :)

Rachel

--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Rachel, one of the SQL statements in our Clinic that people find the
> hardest to optimize is one that has a thing that looks like
> "id_number =
> 1" in the where clause. "id_number" is the table's primary key,
> yet
> the query spends 20 seconds executing a full-table scan. Any guesses?
> 
> It's because "id_number" was actually defined as a varchar2 column.
> Oracle's implicit type coercion converts the predicate into
> "to_number(id_number) = 1". Presto: the PK index is useless.
> 
> This and dozens of other unnecessarily pathological problems await
> people who try to embed too much information into their names. 
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Tuesday, July 30, 2002 8:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> I can see your point, In the data warehouse we are building here, the
> modeler is planning on prefixing tables with the type of table (D_
> for
> dimension tables, F_ for fact, etc)
> 
> Hm, you mean we have to go back and revisit the naming standards that
> they developed? Can I please suffix the column names with an
> indicator
> of the datatype? :)
> 
> The biggest problem is that most management wants "naming
> standards"...
> 
> 
> Rachel
> 
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > I just think it's a waste. You can tell by context what kind of
> thing
> > a
> > thing is. For example, consider: "select a.flarg from bloing a
> where
> > a.croopoo > 7". This can be understood by syntactical context (even
> > with
> > the nonsense names), without having to rename "bloing" to
> > "bloing_table".
> > 
> > Most of the embedding of type names into object names that I've
> seen
> > has
> > been implemented by users who were inexperienced at the time they
> > created the standard. They were worried that without embedding the
> > type
> > name into the object name, they might forget what kind of object it
> > was.
> > ...Most such naming conventions become onerous over time, long
> after
> > you
> > find out that you can find the type of something in the data
> > dictionary,
> > but after it's too late to save the thousands of extra characters
> of
> > typing that'll waste people's lifespans over time.
> > 
> > In my old OFA paper, I made a joke about how we don't embed type
> > names
> > into object names in 

RE: Table Naming Conventions

[Connor McDonald]

Thats when the standards document says:

"Tables: meaningful name"
"Columns: meaningful name"

etc

:-)

Nothing is worse than tables called "TAB_..." with
columns called "COL..."

Ugh!

 --- Rachel Carmichael <[EMAIL PROTECTED]> wrote:
> Cary,
> 
> As a DBA, I tend not to rely on names anyway because
> I believe that
> documentation etc is out of date and incorrect two
> seconds after it is
> completed.
> 
> But for my developers, it does help to have some
> sort of convention
> when they read explain plans, especially if I also
> impose the rule that
> no one can create anything except me. 
> 
> And then there is the unanswerable argument of
> "corporate policy
> dictates we have naming standards" :)
> 
> Rachel
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > Rachel, one of the SQL statements in our Clinic
> that people find the
> > hardest to optimize is one that has a thing that
> looks like
> > "id_number =
> > 1" in the where clause. "id_number" is the
> table's primary key,
> > yet
> > the query spends 20 seconds executing a full-table
> scan. Any guesses?
> > 
> > It's because "id_number" was actually defined as a
> varchar2 column.
> > Oracle's implicit type coercion converts the
> predicate into
> > "to_number(id_number) = 1". Presto: the PK
> index is useless.
> > 
> > This and dozens of other unnecessarily
> pathological problems await
> > people who try to embed too much information into
> their names. 
> > 
> > 
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > 
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart
> Denmark
> > - 2003 Hotsos Symposium on OracleR System
> Performance, Feb 9-12
> > Dallas
> > 
> > 
> > 
> > -Original Message-
> > Carmichael
> > Sent: Tuesday, July 30, 2002 8:09 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > I can see your point, In the data warehouse we are
> building here, the
> > modeler is planning on prefixing tables with the
> type of table (D_
> > for
> > dimension tables, F_ for fact, etc)
> > 
> > Hm, you mean we have to go back and revisit the
> naming standards that
> > they developed? Can I please suffix the column
> names with an
> > indicator
> > of the datatype? :)
> > 
> > The biggest problem is that most management wants
> "naming
> > standards"...
> > 
> > 
> > Rachel
> > 
> > 
> > --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > > I just think it's a waste. You can tell by
> context what kind of
> > thing
> > > a
> > > thing is. For example, consider: "select a.flarg
> from bloing a
> > where
> > > a.croopoo > 7". This can be understood by
> syntactical context (even
> > > with
> > > the nonsense names), without having to rename
> "bloing" to
> > > "bloing_table".
> > > 
> > > Most of the embedding of type names into object
> names that I've
> > seen
> > > has
> > > been implemented by users who were inexperienced
> at the time they
> > > created the standard. They were worried that
> without embedding the
> > > type
> > > name into the object name, they might forget
> what kind of object it
> > > was.
> > > ...Most such naming conventions become onerous
> over time, long
> > after
> > > you
> > > find out that you can find the type of something
> in the data
> > > dictionary,
> > > but after it's too late to save the thousands of
> extra characters
> > of
> > > typing that'll waste people's lifespans over
> time.
> > > 
> > > In my old OFA paper, I made a joke about how we
> don't embed type
> > > names
> > > into object names in daily life, with just a few
> exceptions (Billy
> > > the
> > > Kid, Winnie the Pooh, Atilla the Hun, and the
> younger family
> > members
> > > of
> > > the old Walton Family TV show are a few
> examples). If you have both
> > a
> > > dog and a child named "Rex," though, it's
> probably a good idea to
> > > expect
> > > them both to come when you call. With SQL,
> though, I can't think of
> > a
> 

RE: Table Naming Conventions

[Mercadante, Thomas F]

Cary & All,

I certainly respect everything that's been said, but these are the standards
that I apply to databases that I construct:

Table name : singular, descriptive name of the object, prefixed by
application function abbreviation.  examples:  Wtw_Employment,
Wtw_Employment_History, Wtw_Empl_Day_Schedule.  Of course, all table names
are default capitalized.  The Wtw_ part is redundant, but the next 3 chars
indicate that all the tables are are part of the Employment function.

Primary Keys:  always the full table name followed by _PK

Foreign Keys:  Always the full table name followed by FKnn where nn is a
simple sequence number.

Indexes supporting foreign keys: always the full table name followed by Knn
where nn is a simple sequence number.  note that the index sequenc enumber
matches the foreign key sequence number,

Unique keys: always the full table name followed by UKnn where nn is a
simple sequence number.

Column Names: a descriptive name followed by a suffix that indicates what
type of column it is.  _DATE = date; _CODE = a vc2 code value supported by a
foreign key; _NBR = a number of some kind; _ID = a number supported by a
sequence number - usually the PK for the table or a foreign key; _TXT = vc2
free flow text;  this can be extended for special codes like _YN_CODE = a
vc2 code value containing either the string YES or NO.

I know some people will consider the above a bit anal, but I follow the
above for a couple of reasons:

1).  It gives developers implicit clues about the type of column they are
dealing with, thus it saves development time.
2).  It will give end-user report-writers implicit clues about the kind of
data to expect in the column.
3).  It helps me when I spin reports off of the DD to see if any indexes
might be missing for foreign keys - because the names are simliar in nature.

I know that I could simply tell the above audience to spin reports off of
the DD, but we all know that this will not happen - they will end up coming
to me for documentation on such stuff.

Anywya, my onw long 2cent piece of advice.

Hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, July 31, 2002 12:08 AM
To: Multiple recipients of list ORACLE-L


Rachel, one of the SQL statements in our Clinic that people find the
hardest to optimize is one that has a thing that looks like "id_number =
1" in the where clause. "id_number" is the table's primary key, yet
the query spends 20 seconds executing a full-table scan. Any guesses?

It's because "id_number" was actually defined as a varchar2 column.
Oracle's implicit type coercion converts the predicate into
"to_number(id_number) = 1". Presto: the PK index is useless.

This and dozens of other unnecessarily pathological problems await
people who try to embed too much information into their names. 


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Carmichael
Sent: Tuesday, July 30, 2002 8:09 PM
To: Multiple recipients of list ORACLE-L

I can see your point, In the data warehouse we are building here, the
modeler is planning on prefixing tables with the type of table (D_ for
dimension tables, F_ for fact, etc)

Hm, you mean we have to go back and revisit the naming standards that
they developed? Can I please suffix the column names with an indicator
of the datatype? :)

The biggest problem is that most management wants "naming standards"...


Rachel


--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> I just think it's a waste. You can tell by context what kind of thing
> a
> thing is. For example, consider: "select a.flarg from bloing a where
> a.croopoo > 7". This can be understood by syntactical context (even
> with
> the nonsense names), without having to rename "bloing" to
> "bloing_table".
> 
> Most of the embedding of type names into object names that I've seen
> has
> been implemented by users who were inexperienced at the time they
> created the standard. They were worried that without embedding the
> type
> name into the object name, they might forget what kind of object it
> was.
> ...Most such naming conventions become onerous over time, long after
> you
> find out that you can find the type of something in the data
> dictionary,
> but after it's too late to save the thousands of extra characters of
> typing that'll waste people's lifespans over time.
> 
> In my old OFA paper, I made a joke about how we don't embed type
> names
> into object names in daily life, with just a few exceptions (Billy
> the
> Kid, Winnie the Pooh, Atilla the Hun, and 

RE: Table Naming Conventions

[Rachel Carmichael]

Cary,

As a DBA, I tend not to rely on names anyway because I believe that
documentation etc is out of date and incorrect two seconds after it is
completed.

But for my developers, it does help to have some sort of convention
when they read explain plans, especially if I also impose the rule that
no one can create anything except me. 

And then there is the unanswerable argument of "corporate policy
dictates we have naming standards" :)

Rachel

--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Rachel, one of the SQL statements in our Clinic that people find the
> hardest to optimize is one that has a thing that looks like
> "id_number =
> 1" in the where clause. "id_number" is the table's primary key,
> yet
> the query spends 20 seconds executing a full-table scan. Any guesses?
> 
> It's because "id_number" was actually defined as a varchar2 column.
> Oracle's implicit type coercion converts the predicate into
> "to_number(id_number) = 1". Presto: the PK index is useless.
> 
> This and dozens of other unnecessarily pathological problems await
> people who try to embed too much information into their names. 
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Tuesday, July 30, 2002 8:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> I can see your point, In the data warehouse we are building here, the
> modeler is planning on prefixing tables with the type of table (D_
> for
> dimension tables, F_ for fact, etc)
> 
> Hm, you mean we have to go back and revisit the naming standards that
> they developed? Can I please suffix the column names with an
> indicator
> of the datatype? :)
> 
> The biggest problem is that most management wants "naming
> standards"...
> 
> 
> Rachel
> 
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > I just think it's a waste. You can tell by context what kind of
> thing
> > a
> > thing is. For example, consider: "select a.flarg from bloing a
> where
> > a.croopoo > 7". This can be understood by syntactical context (even
> > with
> > the nonsense names), without having to rename "bloing" to
> > "bloing_table".
> > 
> > Most of the embedding of type names into object names that I've
> seen
> > has
> > been implemented by users who were inexperienced at the time they
> > created the standard. They were worried that without embedding the
> > type
> > name into the object name, they might forget what kind of object it
> > was.
> > ...Most such naming conventions become onerous over time, long
> after
> > you
> > find out that you can find the type of something in the data
> > dictionary,
> > but after it's too late to save the thousands of extra characters
> of
> > typing that'll waste people's lifespans over time.
> > 
> > In my old OFA paper, I made a joke about how we don't embed type
> > names
> > into object names in daily life, with just a few exceptions (Billy
> > the
> > Kid, Winnie the Pooh, Atilla the Hun, and the younger family
> members
> > of
> > the old Walton Family TV show are a few examples). If you have both
> a
> > dog and a child named "Rex," though, it's probably a good idea to
> > expect
> > them both to come when you call. With SQL, though, I can't think of
> a
> > case in which it's not easy to tell by syntactic context what kind
> of
> > thing you're talking about...
> > 
> > 
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > 
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > Dallas
> > 
> > 
> > 
> > -Original Message-
> > Carmichael
> > Sent: Tuesday, July 30, 2002 4:49 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > Cary,
> > 
> > you said 
> > "* Don't embed the object type in the object's name. I used to see
> > this
> > all the time with tablespaces called XYZ_TS, indexes called
> > IND_THING,
> > and so on."
> > 
> > what's your logic behind 

Re: Table Naming Conventions

[Mladen Gogala]

That's a classic one, taught in various courses ever since version 5.
The most famous example was

select...
where date_column='12-31-99'

where date_column would be implicitly converted to varchar2. A little 
'explain
plan' effort  and all the confusion is easily avoided.




On 2002.07.31 00:08 Cary Millsap wrote:
> Rachel, one of the SQL statements in our Clinic that people find the
> hardest to optimize is one that has a thing that looks like "id_number
> =
> 1" in the where clause. "id_number" is the table's primary key,
> yet
> the query spends 20 seconds executing a full-table scan. Any guesses?
> 
> It's because "id_number" was actually defined as a varchar2 column.
> Oracle's implicit type coercion converts the predicate into
> "to_number(id_number) = 1". Presto: the PK index is useless.
> 
> This and dozens of other unnecessarily pathological problems await
> people who try to embed too much information into their names.
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Tuesday, July 30, 2002 8:09 PM
> To: Multiple recipients of list ORACLE-L
> 
> I can see your point, In the data warehouse we are building here, the
> modeler is planning on prefixing tables with the type of table (D_ for
> dimension tables, F_ for fact, etc)
> 
> Hm, you mean we have to go back and revisit the naming standards that
> they developed? Can I please suffix the column names with an indicator
> of the datatype? :)
> 
> The biggest problem is that most management wants "naming
> standards"...
> 
> 
> Rachel
> 
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > I just think it's a waste. You can tell by context what kind of
> thing
> > a
> > thing is. For example, consider: "select a.flarg from bloing a where
> > a.croopoo > 7". This can be understood by syntactical context (even
> > with
> > the nonsense names), without having to rename "bloing" to
> > "bloing_table".
> >
> > Most of the embedding of type names into object names that I've seen
> > has
> > been implemented by users who were inexperienced at the time they
> > created the standard. They were worried that without embedding the
> > type
> > name into the object name, they might forget what kind of object it
> > was.
> > ...Most such naming conventions become onerous over time, long after
> > you
> > find out that you can find the type of something in the data
> > dictionary,
> > but after it's too late to save the thousands of extra characters of
> > typing that'll waste people's lifespans over time.
> >
> > In my old OFA paper, I made a joke about how we don't embed type
> > names
> > into object names in daily life, with just a few exceptions (Billy
> > the
> > Kid, Winnie the Pooh, Atilla the Hun, and the younger family members
> > of
> > the old Walton Family TV show are a few examples). If you have both
> a
> > dog and a child named "Rex," though, it's probably a good idea to
> > expect
> > them both to come when you call. With SQL, though, I can't think of
> a
> > case in which it's not easy to tell by syntactic context what kind
> of
> > thing you're talking about...
> >
> >
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> >
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > Dallas
> >
> >
> >
> > -Original Message-
> > Carmichael
> > Sent: Tuesday, July 30, 2002 4:49 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > Cary,
> >
> > you said
> > "* Don't embed the object type in the object's name. I used to see
> > this
> > all the time with tablespaces called XYZ_TS, indexes called
> > IND_THING,
> > and so on."
> >
> > what's your logic behind that?
> >
> > Rachel
> >
> >
> >
> > --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > > Here's a start. Not a checklist by any means, just kind of a micro
> > >

RE: Table Naming Conventions

[Cary Millsap]

Rachel, one of the SQL statements in our Clinic that people find the
hardest to optimize is one that has a thing that looks like "id_number =
1" in the where clause. "id_number" is the table's primary key, yet
the query spends 20 seconds executing a full-table scan. Any guesses?

It's because "id_number" was actually defined as a varchar2 column.
Oracle's implicit type coercion converts the predicate into
"to_number(id_number) = 1". Presto: the PK index is useless.

This and dozens of other unnecessarily pathological problems await
people who try to embed too much information into their names. 


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Carmichael
Sent: Tuesday, July 30, 2002 8:09 PM
To: Multiple recipients of list ORACLE-L

I can see your point, In the data warehouse we are building here, the
modeler is planning on prefixing tables with the type of table (D_ for
dimension tables, F_ for fact, etc)

Hm, you mean we have to go back and revisit the naming standards that
they developed? Can I please suffix the column names with an indicator
of the datatype? :)

The biggest problem is that most management wants "naming standards"...


Rachel


--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> I just think it's a waste. You can tell by context what kind of thing
> a
> thing is. For example, consider: "select a.flarg from bloing a where
> a.croopoo > 7". This can be understood by syntactical context (even
> with
> the nonsense names), without having to rename "bloing" to
> "bloing_table".
> 
> Most of the embedding of type names into object names that I've seen
> has
> been implemented by users who were inexperienced at the time they
> created the standard. They were worried that without embedding the
> type
> name into the object name, they might forget what kind of object it
> was.
> ...Most such naming conventions become onerous over time, long after
> you
> find out that you can find the type of something in the data
> dictionary,
> but after it's too late to save the thousands of extra characters of
> typing that'll waste people's lifespans over time.
> 
> In my old OFA paper, I made a joke about how we don't embed type
> names
> into object names in daily life, with just a few exceptions (Billy
> the
> Kid, Winnie the Pooh, Atilla the Hun, and the younger family members
> of
> the old Walton Family TV show are a few examples). If you have both a
> dog and a child named "Rex," though, it's probably a good idea to
> expect
> them both to come when you call. With SQL, though, I can't think of a
> case in which it's not easy to tell by syntactic context what kind of
> thing you're talking about...
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Tuesday, July 30, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> 
> Cary,
> 
> you said 
> "* Don't embed the object type in the object's name. I used to see
> this
> all the time with tablespaces called XYZ_TS, indexes called
> IND_THING,
> and so on."
> 
> what's your logic behind that? 
> 
> Rachel
> 
> 
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > Here's a start. Not a checklist by any means, just kind of a micro
> > pet
> > peeves list.
> > 
> > * Decide today whether table names will be singular or plural. Do
> you
> > want a THING (singular) table? Or a THINGS (plural) table?
> > 
> > * Don't use case-sensitive names. E.g., use THING (without quotes)
> in
> > your CREATE (DDL) statement, which can be spelled "THING", "thing",
> > "Thing", or even "tHiNG" in your developers' SQL. But don't make
> > developers type stuff like this...
> > 
> > select "Name" from "Thing" where "Id" = y  /* won't work
> without
> > ""
> > */
> > 
> > * Don't embed the object type in the object's name. I used to see
> > this
> > all the time with tablespaces called XYZ_TS, indexes called
> > IND_THING,
> > and so on.

RE: Table Naming Conventions

[Cary Millsap]

I don't like such prefixes because they make it easier to lie or make
mistakes. I've seen people try for hours to figure out why something
doesn't make sense, only to find out that the index whose name has a 'U'
in it actually wasn't created with the "UNIQUE" attribute.

Names falsely cause reliance that cannot be trusted. Making reports on
such things easier to run is, to my mind, a better idea than integrating
too much information into naming standards.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Sent: Tuesday, July 30, 2002 8:09 PM
To: Multiple recipients of list ORACLE-L

Hmmm.  Seems I have to play devil's advocate on this one.  So, you see
no purpose to index names such as:

P_EMPLOYEE for a Primary key on the employee table
or UM_METER_CODE for a unique index on the meter table meter_code column
or FE_DEPT for a foreign key index on the employee table dept column
or IIE_LOG_DATE for a non-unique index on the import_export table
log_date column?

The first letter doesn't help me figure out that the object is an index,
but it sure helps to show what the index is being used for.

Constraint naming is another area where we use a one character prefix to
identify the constraint type, in part to conform to index naming.

The keeper of standards here (not me) has also imposed prefix
conventions on global temporary tables and on selected classes of tables
such as work tables or external cross reference tables.  It does help
keep the developers in line.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE!  What can this mean?


-Original Message-
Sent: Tuesday, July 30, 2002 4:58 PM
To: Multiple recipients of list ORACLE-L


You already know it's an index, why would you include that as part of
the 
name?

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/30/2002 02:29 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
cc: 
Subject:RE: Table Naming Conventions


My supervisor/client wants object types in names - except tables like I_

for indexes.  Why do you say stay away from this?
-Original Message- 
Sent: Tuesday, July 30, 2002 5:10 PM 
To: Multiple recipients of list ORACLE-L 

Here's a start. Not a checklist by any means, just kind of a micro pet 
peeves list. 
* Decide today whether table names will be singular or plural. Do you 
want a THING (singular) table? Or a THINGS (plural) table? 
* Don't use case-sensitive names. E.g., use THING (without quotes) in 
your CREATE (DDL) statement, which can be spelled "THING", "thing", 
"Thing", or even "tHiNG" in your developers' SQL. But don't make 
developers type stuff like this... 
select "Name" from "Thing" where "Id" = y  /* won't work without "" 
*/ 
* Don't embed the object type in the object's name. I used to see this 
all the time with tablespaces called XYZ_TS, indexes called IND_THING, 
and so on. 
* Decide today whether you want to abbreviate or not. If you do, then 
construct a formal, standard, consistent list of accepted abbreviations.

Don't name one table CUSTOMER_THING and another CUST_HISTORY. 

Cary Millsap 
Hotsos Enterprises, Ltd. 
http://www.hotsos.com 
Upcoming events: 
- NCOAUG Training Day, Aug 16 Chicago 
- Miracle Database Forum, Sep 20-22 Middlefart Denmark 
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas 


-Original Message----- 
Chambers 
Sent: Tuesday, July 30, 2002 3:37 PM 
To: Multiple recipients of list ORACLE-L 
All... 
Will some of you please provide some insight on your table naming 
conventions?  I'm in the very early planning stages of what will likely 
be a large and complex schema (IT asset inventory).  I have a chance to 
start it correctly.  TIA 
Gary Chambers 
//- 
// Lucent Technologies GIO/Unix 
// 4 Robbins Road, Westford, MA 01886 
// 978-399-0481 / 888-480-6924 (Pager) 
// Nothing fancy and nothing Microsoft 
//- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Gary Chambers 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California-- Public Internet access / Mailing Lists 
 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing 

RE: Table Naming Conventions

[Rachel Carmichael]

I can see your point, In the data warehouse we are building here, the
modeler is planning on prefixing tables with the type of table (D_ for
dimension tables, F_ for fact, etc)

Hm, you mean we have to go back and revisit the naming standards that
they developed? Can I please suffix the column names with an indicator
of the datatype? :)

The biggest problem is that most management wants "naming standards"...


Rachel


--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> I just think it's a waste. You can tell by context what kind of thing
> a
> thing is. For example, consider: "select a.flarg from bloing a where
> a.croopoo > 7". This can be understood by syntactical context (even
> with
> the nonsense names), without having to rename "bloing" to
> "bloing_table".
> 
> Most of the embedding of type names into object names that I've seen
> has
> been implemented by users who were inexperienced at the time they
> created the standard. They were worried that without embedding the
> type
> name into the object name, they might forget what kind of object it
> was.
> ...Most such naming conventions become onerous over time, long after
> you
> find out that you can find the type of something in the data
> dictionary,
> but after it's too late to save the thousands of extra characters of
> typing that'll waste people's lifespans over time.
> 
> In my old OFA paper, I made a joke about how we don't embed type
> names
> into object names in daily life, with just a few exceptions (Billy
> the
> Kid, Winnie the Pooh, Atilla the Hun, and the younger family members
> of
> the old Walton Family TV show are a few examples). If you have both a
> dog and a child named "Rex," though, it's probably a good idea to
> expect
> them both to come when you call. With SQL, though, I can't think of a
> case in which it's not easy to tell by syntactic context what kind of
> thing you're talking about...
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Carmichael
> Sent: Tuesday, July 30, 2002 4:49 PM
> To: Multiple recipients of list ORACLE-L
> 
> Cary,
> 
> you said 
> "* Don't embed the object type in the object's name. I used to see
> this
> all the time with tablespaces called XYZ_TS, indexes called
> IND_THING,
> and so on."
> 
> what's your logic behind that? 
> 
> Rachel
> 
> 
> 
> --- Cary Millsap <[EMAIL PROTECTED]> wrote:
> > Here's a start. Not a checklist by any means, just kind of a micro
> > pet
> > peeves list.
> > 
> > * Decide today whether table names will be singular or plural. Do
> you
> > want a THING (singular) table? Or a THINGS (plural) table?
> > 
> > * Don't use case-sensitive names. E.g., use THING (without quotes)
> in
> > your CREATE (DDL) statement, which can be spelled "THING", "thing",
> > "Thing", or even "tHiNG" in your developers' SQL. But don't make
> > developers type stuff like this...
> > 
> > select "Name" from "Thing" where "Id" = y  /* won't work
> without
> > ""
> > */
> > 
> > * Don't embed the object type in the object's name. I used to see
> > this
> > all the time with tablespaces called XYZ_TS, indexes called
> > IND_THING,
> > and so on.
> > 
> > * Decide today whether you want to abbreviate or not. If you do,
> then
> > construct a formal, standard, consistent list of accepted
> > abbreviations.
> > Don't name one table CUSTOMER_THING and another CUST_HISTORY.
> > 
> > 
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > 
> > Upcoming events:
> > - NCOAUG Training Day, Aug 16 Chicago
> > - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> > - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> > Dallas
> > 
> > 
> > 
> > -Original Message-
> > Chambers
> > Sent: Tuesday, July 30, 2002 3:37 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > All...
> > 
> > Will some of you please provide some insight on your table naming
> > conventions?  I'm in the very early planning stages of what will
> > likely
> > b

RE: Table Naming Conventions

[CHAN Chor Ling Catherine (CSC)]

Hi Gary,

In our environment, there are many applications in one database. We always
create public synonym for all the tables. To avoid confusion, all the tables
have to be tagged with the application prefix .eg. Student System has the
prefix STD, so the tables will be STD_XXX where XXX is the table name and
the public synonym is STDXXX. We don't have any rules for the table name
XXX.

Hope it helps.

Regds,
Catherine
-Original Message-
From:   Gary Chambers [mailto:[EMAIL PROTECTED]]
Sent:   Wednesday, July 31, 2002 4:37 AM
To: Multiple recipients of list ORACLE-L
Subject:Table Naming Conventions

All...

Will some of you please provide some insight on your table
naming
    conventions?  I'm in the very early planning stages of what
will likely
be a large and complex schema (IT asset inventory).  I have
a chance to
start it correctly.  TIA

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858)
538-5051
San Diego, California-- Public Internet access /
Mailing Lists


To REMOVE yourself from this mailing list, send an E-Mail
message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).
You may
also send the HELP command for other information (like
subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CHAN Chor Ling Catherine (CSC)
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[kkennedy]

Hmmm.  Seems I have to play devil's advocate on this one.  So, you see no purpose to 
index names such as:

P_EMPLOYEE for a Primary key on the employee table
or UM_METER_CODE for a unique index on the meter table meter_code column
or FE_DEPT for a foreign key index on the employee table dept column
or IIE_LOG_DATE for a non-unique index on the import_export table log_date column?

The first letter doesn't help me figure out that the object is an index, but it sure 
helps to show what the index is being used for.

Constraint naming is another area where we use a one character prefix to identify the 
constraint type, in part to conform to index naming.

The keeper of standards here (not me) has also imposed prefix conventions on global 
temporary tables and on selected classes of tables such as work tables or external 
cross reference tables.  It does help keep the developers in line.

Kevin Kennedy
First Point Energy Corporation

If you take RAC out of Oracle you get OLE!  What can this mean?


-Original Message-
Sent: Tuesday, July 30, 2002 4:58 PM
To: Multiple recipients of list ORACLE-L


You already know it's an index, why would you include that as part of the 
name?

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/30/2002 02:29 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:    RE: Table Naming Conventions


My supervisor/client wants object types in names - except tables like I_ 
for indexes.  Why do you say stay away from this?
-Original Message- 
Sent: Tuesday, July 30, 2002 5:10 PM 
To: Multiple recipients of list ORACLE-L 

Here's a start. Not a checklist by any means, just kind of a micro pet 
peeves list. 
* Decide today whether table names will be singular or plural. Do you 
want a THING (singular) table? Or a THINGS (plural) table? 
* Don't use case-sensitive names. E.g., use THING (without quotes) in 
your CREATE (DDL) statement, which can be spelled "THING", "thing", 
"Thing", or even "tHiNG" in your developers' SQL. But don't make 
developers type stuff like this... 
select "Name" from "Thing" where "Id" = y  /* won't work without "" 
*/ 
* Don't embed the object type in the object's name. I used to see this 
all the time with tablespaces called XYZ_TS, indexes called IND_THING, 
and so on. 
* Decide today whether you want to abbreviate or not. If you do, then 
construct a formal, standard, consistent list of accepted abbreviations. 
Don't name one table CUSTOMER_THING and another CUST_HISTORY. 

Cary Millsap 
Hotsos Enterprises, Ltd. 
http://www.hotsos.com 
Upcoming events: 
- NCOAUG Training Day, Aug 16 Chicago 
- Miracle Database Forum, Sep 20-22 Middlefart Denmark 
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas 


-Original Message- 
Chambers 
Sent: Tuesday, July 30, 2002 3:37 PM 
To: Multiple recipients of list ORACLE-L 
All... 
Will some of you please provide some insight on your table naming 
conventions?  I'm in the very early planning stages of what will likely 
be a large and complex schema (IT asset inventory).  I have a chance to 
start it correctly.  TIA 
Gary Chambers 
//- 
// Lucent Technologies GIO/Unix 
// 4 Robbins Road, Westford, MA 01886 
// 978-399-0481 / 888-480-6924 (Pager) 
// Nothing fancy and nothing Microsoft 
//- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Gary Chambers 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California-- Public Internet access / Mailing Lists 
 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Cary Millsap 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California-- Public Internet access / Mailing Lists 
 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.

RE: Table Naming Conventions

[Jared . Still]

You already know it's an index, why would you include that as part of the 
name?

Jared





[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/30/2002 02:29 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:    RE: Table Naming Conventions


My supervisor/client wants object types in names - except tables like I_ 
for indexes.  Why do you say stay away from this?
-Original Message- 
Sent: Tuesday, July 30, 2002 5:10 PM 
To: Multiple recipients of list ORACLE-L 

Here's a start. Not a checklist by any means, just kind of a micro pet 
peeves list. 
* Decide today whether table names will be singular or plural. Do you 
want a THING (singular) table? Or a THINGS (plural) table? 
* Don't use case-sensitive names. E.g., use THING (without quotes) in 
your CREATE (DDL) statement, which can be spelled "THING", "thing", 
"Thing", or even "tHiNG" in your developers' SQL. But don't make 
developers type stuff like this... 
select "Name" from "Thing" where "Id" = y  /* won't work without "" 
*/ 
* Don't embed the object type in the object's name. I used to see this 
all the time with tablespaces called XYZ_TS, indexes called IND_THING, 
and so on. 
* Decide today whether you want to abbreviate or not. If you do, then 
construct a formal, standard, consistent list of accepted abbreviations. 
Don't name one table CUSTOMER_THING and another CUST_HISTORY. 

Cary Millsap 
Hotsos Enterprises, Ltd. 
http://www.hotsos.com 
Upcoming events: 
- NCOAUG Training Day, Aug 16 Chicago 
- Miracle Database Forum, Sep 20-22 Middlefart Denmark 
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas 


-Original Message- 
Chambers 
Sent: Tuesday, July 30, 2002 3:37 PM 
To: Multiple recipients of list ORACLE-L 
All... 
Will some of you please provide some insight on your table naming 
conventions?  I'm in the very early planning stages of what will likely 
be a large and complex schema (IT asset inventory).  I have a chance to 
start it correctly.  TIA 
Gary Chambers 
//- 
// Lucent Technologies GIO/Unix 
// 4 Robbins Road, Westford, MA 01886 
// 978-399-0481 / 888-480-6924 (Pager) 
// Nothing fancy and nothing Microsoft 
//- 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Gary Chambers 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California-- Public Internet access / Mailing Lists 
 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Cary Millsap 
  INET: [EMAIL PROTECTED] 
Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051 
San Diego, California-- Public Internet access / Mailing Lists 
 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Stephen Andert]

I ran into a problem with this once.  I had a procedure that I used to
monitor database statistics that I named monitor_stats.  I stored that
data in a table called monitor_stats (OK, I should have named the table
stats, but that seemed too generic).  With a procedure and table named
the same, the procedure wouldn't compile.  When I renamed the procedure
to monitor_stats_proc, it worked.  

But I agree that keeping object types out of their names is a good
idea.

dba_Stephen
Stephen_dba
Stephen

>>> [EMAIL PROTECTED] 07/30/02 03:53PM >>>
I just think it's a waste. You can tell by context what kind of thing
a
thing is. For example, consider: "select a.flarg from bloing a where
a.croopoo > 7". This can be understood by syntactical context (even
with
the nonsense names), without having to rename "bloing" to
"bloing_table".

Most of the embedding of type names into object names that I've seen
has
been implemented by users who were inexperienced at the time they
created the standard. They were worried that without embedding the
type
name into the object name, they might forget what kind of object it
was.
...Most such naming conventions become onerous over time, long after
you
find out that you can find the type of something in the data
dictionary,
but after it's too late to save the thousands of extra characters of
typing that'll waste people's lifespans over time.

In my old OFA paper, I made a joke about how we don't embed type names
into object names in daily life, with just a few exceptions (Billy the
Kid, Winnie the Pooh, Atilla the Hun, and the younger family members
of
the old Walton Family TV show are a few examples). If you have both a
dog and a child named "Rex," though, it's probably a good idea to
expect
them both to come when you call. With SQL, though, I can't think of a
case in which it's not easy to tell by syntactic context what kind of
thing you're talking about...


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com 

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Carmichael
Sent: Tuesday, July 30, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L

Cary,

you said 
"* Don't embed the object type in the object's name. I used to see
this
all the time with tablespaces called XYZ_TS, indexes called IND_THING,
and so on."

what's your logic behind that? 

Rachel



--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Here's a start. Not a checklist by any means, just kind of a micro
> pet
> peeves list.
> 
> * Decide today whether table names will be singular or plural. Do
you
> want a THING (singular) table? Or a THINGS (plural) table?
> 
> * Don't use case-sensitive names. E.g., use THING (without quotes)
in
> your CREATE (DDL) statement, which can be spelled "THING", "thing",
> "Thing", or even "tHiNG" in your developers' SQL. But don't make
> developers type stuff like this...
> 
> select "Name" from "Thing" where "Id" = y  /* won't work without
> ""
> */
> 
> * Don't embed the object type in the object's name. I used to see
> this
> all the time with tablespaces called XYZ_TS, indexes called
> IND_THING,
> and so on.
> 
> * Decide today whether you want to abbreviate or not. If you do,
then
> construct a formal, standard, consistent list of accepted
> abbreviations.
> Don't name one table CUSTOMER_THING and another CUST_HISTORY.
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com 
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Chambers
> Sent: Tuesday, July 30, 2002 3:37 PM
> To: Multiple recipients of list ORACLE-L
> 
> All...
> 
> Will some of you please provide some insight on your table naming
> conventions?  I'm in the very early planning stages of what will
> likely
> be a large and complex schema (IT asset inventory).  I have a chance
> to
> start it correctly.  TIA
> 
> Gary Chambers
> 
> //-
> // Lucent Technologies GIO/Unix
> // 4 Robbins Road, Westford, MA 01886
> // 978-399-0481 / 888-480-6924 (Pager)
> // Nothing fancy and nothing Microsoft
> //-
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> -- 
> Author: Gary Chambers
>   INET: [EMA

RE: Table Naming Conventions

[Cary Millsap]

I just think it's a waste. You can tell by context what kind of thing a
thing is. For example, consider: "select a.flarg from bloing a where
a.croopoo > 7". This can be understood by syntactical context (even with
the nonsense names), without having to rename "bloing" to
"bloing_table".

Most of the embedding of type names into object names that I've seen has
been implemented by users who were inexperienced at the time they
created the standard. They were worried that without embedding the type
name into the object name, they might forget what kind of object it was.
...Most such naming conventions become onerous over time, long after you
find out that you can find the type of something in the data dictionary,
but after it's too late to save the thousands of extra characters of
typing that'll waste people's lifespans over time.

In my old OFA paper, I made a joke about how we don't embed type names
into object names in daily life, with just a few exceptions (Billy the
Kid, Winnie the Pooh, Atilla the Hun, and the younger family members of
the old Walton Family TV show are a few examples). If you have both a
dog and a child named "Rex," though, it's probably a good idea to expect
them both to come when you call. With SQL, though, I can't think of a
case in which it's not easy to tell by syntactic context what kind of
thing you're talking about...


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Carmichael
Sent: Tuesday, July 30, 2002 4:49 PM
To: Multiple recipients of list ORACLE-L

Cary,

you said 
"* Don't embed the object type in the object's name. I used to see this
all the time with tablespaces called XYZ_TS, indexes called IND_THING,
and so on."

what's your logic behind that? 

Rachel



--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Here's a start. Not a checklist by any means, just kind of a micro
> pet
> peeves list.
> 
> * Decide today whether table names will be singular or plural. Do you
> want a THING (singular) table? Or a THINGS (plural) table?
> 
> * Don't use case-sensitive names. E.g., use THING (without quotes) in
> your CREATE (DDL) statement, which can be spelled "THING", "thing",
> "Thing", or even "tHiNG" in your developers' SQL. But don't make
> developers type stuff like this...
> 
> select "Name" from "Thing" where "Id" = y  /* won't work without
> ""
> */
> 
> * Don't embed the object type in the object's name. I used to see
> this
> all the time with tablespaces called XYZ_TS, indexes called
> IND_THING,
> and so on.
> 
> * Decide today whether you want to abbreviate or not. If you do, then
> construct a formal, standard, consistent list of accepted
> abbreviations.
> Don't name one table CUSTOMER_THING and another CUST_HISTORY.
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Chambers
> Sent: Tuesday, July 30, 2002 3:37 PM
> To: Multiple recipients of list ORACLE-L
> 
> All...
> 
> Will some of you please provide some insight on your table naming
> conventions?  I'm in the very early planning stages of what will
> likely
> be a large and complex schema (IT asset inventory).  I have a chance
> to
> start it correctly.  TIA
> 
> Gary Chambers
> 
> //-
> // Lucent Technologies GIO/Unix
> // 4 Robbins Road, Westford, MA 01886
> // 978-399-0481 / 888-480-6924 (Pager)
> // Nothing fancy and nothing Microsoft
> //-
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Gary Chambers
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command

RE: Table Naming Conventions

[Rachel Carmichael]

Cary,

you said 
"* Don't embed the object type in the object's name. I used to see this
all the time with tablespaces called XYZ_TS, indexes called IND_THING,
and so on."

what's your logic behind that? 

Rachel



--- Cary Millsap <[EMAIL PROTECTED]> wrote:
> Here's a start. Not a checklist by any means, just kind of a micro
> pet
> peeves list.
> 
> * Decide today whether table names will be singular or plural. Do you
> want a THING (singular) table? Or a THINGS (plural) table?
> 
> * Don't use case-sensitive names. E.g., use THING (without quotes) in
> your CREATE (DDL) statement, which can be spelled "THING", "thing",
> "Thing", or even "tHiNG" in your developers' SQL. But don't make
> developers type stuff like this...
> 
> select "Name" from "Thing" where "Id" = y  /* won't work without
> ""
> */
> 
> * Don't embed the object type in the object's name. I used to see
> this
> all the time with tablespaces called XYZ_TS, indexes called
> IND_THING,
> and so on.
> 
> * Decide today whether you want to abbreviate or not. If you do, then
> construct a formal, standard, consistent list of accepted
> abbreviations.
> Don't name one table CUSTOMER_THING and another CUST_HISTORY.
> 
> 
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> 
> Upcoming events:
> - NCOAUG Training Day, Aug 16 Chicago
> - Miracle Database Forum, Sep 20-22 Middlefart Denmark
> - 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12
> Dallas
> 
> 
> 
> -Original Message-
> Chambers
> Sent: Tuesday, July 30, 2002 3:37 PM
> To: Multiple recipients of list ORACLE-L
> 
> All...
> 
> Will some of you please provide some insight on your table naming
> conventions?  I'm in the very early planning stages of what will
> likely
> be a large and complex schema (IT asset inventory).  I have a chance
> to
> start it correctly.  TIA
> 
> Gary Chambers
> 
> //-
> // Lucent Technologies GIO/Unix
> // 4 Robbins Road, Westford, MA 01886
> // 978-399-0481 / 888-480-6924 (Pager)
> // Nothing fancy and nothing Microsoft
> //-
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Gary Chambers
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Cary Millsap
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Paula_Stankus]

Title: RE: Table Naming Conventions





My supervisor/client wants object types in names - except tables like I_ for indexes.  Why do you say stay away from this?

-Original Message-
From: Cary Millsap [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, July 30, 2002 5:10 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Table Naming Conventions



Here's a start. Not a checklist by any means, just kind of a micro pet
peeves list.


* Decide today whether table names will be singular or plural. Do you
want a THING (singular) table? Or a THINGS (plural) table?


* Don't use case-sensitive names. E.g., use THING (without quotes) in
your CREATE (DDL) statement, which can be spelled "THING", "thing",
"Thing", or even "tHiNG" in your developers' SQL. But don't make
developers type stuff like this...


    select "Name" from "Thing" where "Id" = y  /* won't work without ""
*/


* Don't embed the object type in the object's name. I used to see this
all the time with tablespaces called XYZ_TS, indexes called IND_THING,
and so on.


* Decide today whether you want to abbreviate or not. If you do, then
construct a formal, standard, consistent list of accepted abbreviations.
Don't name one table CUSTOMER_THING and another CUST_HISTORY.



Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com


Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas




-Original Message-
Chambers
Sent: Tuesday, July 30, 2002 3:37 PM
To: Multiple recipients of list ORACLE-L


All...


Will some of you please provide some insight on your table naming
conventions?  I'm in the very early planning stages of what will likely
be a large and complex schema (IT asset inventory).  I have a chance to
start it correctly.  TIA


Gary Chambers


//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]


Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California    -- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Table Naming Conventions

[Cary Millsap]

Here's a start. Not a checklist by any means, just kind of a micro pet
peeves list.

* Decide today whether table names will be singular or plural. Do you
want a THING (singular) table? Or a THINGS (plural) table?

* Don't use case-sensitive names. E.g., use THING (without quotes) in
your CREATE (DDL) statement, which can be spelled "THING", "thing",
"Thing", or even "tHiNG" in your developers' SQL. But don't make
developers type stuff like this...

select "Name" from "Thing" where "Id" = y  /* won't work without ""
*/

* Don't embed the object type in the object's name. I used to see this
all the time with tablespaces called XYZ_TS, indexes called IND_THING,
and so on.

* Decide today whether you want to abbreviate or not. If you do, then
construct a formal, standard, consistent list of accepted abbreviations.
Don't name one table CUSTOMER_THING and another CUST_HISTORY.


Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- NCOAUG Training Day, Aug 16 Chicago
- Miracle Database Forum, Sep 20-22 Middlefart Denmark
- 2003 Hotsos Symposium on OracleR System Performance, Feb 9-12 Dallas



-Original Message-
Chambers
Sent: Tuesday, July 30, 2002 3:37 PM
To: Multiple recipients of list ORACLE-L

All...

Will some of you please provide some insight on your table naming
conventions?  I'm in the very early planning stages of what will likely
be a large and complex schema (IT asset inventory).  I have a chance to
start it correctly.  TIA

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cary Millsap
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Table Naming Conventions

[Gary Chambers]

All...

Will some of you please provide some insight on your table naming
conventions?  I'm in the very early planning stages of what will likely
be a large and complex schema (IT asset inventory).  I have a chance to
start it correctly.  TIA

Gary Chambers

//-
// Lucent Technologies GIO/Unix
// 4 Robbins Road, Westford, MA 01886
// 978-399-0481 / 888-480-6924 (Pager)
// Nothing fancy and nothing Microsoft
//-

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gary Chambers
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: naming conventions for Oracle/Unix vs. SQL Server

[Daniel Wisser]

hi!

from my experience of haveing the same data or data model
on different DB brands and platforms it is wise wiser wisest
- cause you never know what the management is going to
buy next - to stick to the following naming conventions
for identifiers:

+ all identifiers in uppercase
+ not more than 30 chars (even if MS$SQL offers you 128)
  (if there's an old DB2 on the mainframe around, even: 18)
+ start with a char
+ do NOT - even not in quotes - use reserved words: INSERT, VIEW etc.
+ be careful with other characters than letters and numbers
  many developers use '_' only

this will make life easier

daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Daniel Wisser
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: naming conventions for Oracle/Unix vs. SQL Server

[Jared . Still]

Unless you are talking about Java in and Oracle database, in which
case it is case sensitive.

e.g.

select owner, object_name, object_type
from dba_objects
where object_type like 'JAVA%';

Jared






"Karniotis, Stephen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/29/2002 09:15 AM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
    Subject:RE: naming conventions for Oracle/Unix vs. SQL Server


Paula:
 
  The mixed case for Oracle does not matter, as Oracle is not case 
sensitive.  The column names are stored in uppercase within the data 
dictionary.  However, for SQL Server, the case sensitivity of column names 
is crucial. 
 
  Another black eye for MSFT.
 
Thank You
 
Stephen P. Karniotis
Product Architect
Compuware Corporation
Direct:   (248) 865-4350
Mobile:  (248) 408-2918
Email:  [EMAIL PROTECTED]
Web:www.compuware.com
 
-Original Message-
Sent: Monday, July 29, 2002 11:59 AM
To: Multiple recipients of list ORACLE-L
 
Guys, 
Please help.  I work in an organization where we have both SQL Server on 
NT and Oracle on Unix.  SQL Server and developers who are used to GUI's in 
NT like column names to have mixed case with no underscores.  The Unix 
folk - like myself prefer underscores and one case.  Is there any reason 
not to adopt mixed case for Oracle?  Is this really just what I am used 
to?  I have been using this standard for so long that it maybe the reasons 
I adopted it do not any longer exist or are not as compelling as 
developer's today are more comfortable with mixed case. 
Help! 



The contents of this e-mail are intended for the named addressee only. It 
contains information that may be confidential. Unless you are the named 
addressee or an authorized designee, you may not copy or use it, or 
disclose it to anyone else. If you received it in error please notify us 
immediately and then destroy it. 



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: naming conventions for Oracle/Unix vs. SQL Server

[Suzy Vordos]

I come from a Unix/Shell/Perl/Java background where exact case match is
important.  Which is why it's s apparent that MS's lack of case
sensitivity bugs me :)  

I recently had a similar discussion with a developer, who was absolutely
puzzled that case-sensitivity was an issue because his only experience
was with Microsoft.   

Suzy

STEVE OLLIG wrote:
> 
> Suzy - it isn't just MS_LAND that uses mixed-case.  i've seen more than one
> non-M$ shop take advantage of that in their namingConvetions.  shell
> scripts, perl, java, and even other non-M$ databases - Sybase on HP-UX for
> example.
> 
> i do however agree with the rest of the posts - probably not a good idea in
> oracleLand ;)
> 
> funny how passionate some can be about small things like this.  we had a
> rather heated debate on whether this:
> 
> try {
>   stuff();
>   more.stuff();
>   }
> 
> or this:
> 
> try
> {
>   stuff();
>   more.stuff();
> }
> 
> was to be in our java standards recently.  FWIW, the former won out.
> 
> -Original Message-
> Sent: Monday, July 29, 2002 12:39 PM
> To: Multiple recipients of list ORACLE-L
> 
> Definately underscores, if simply just to break the habit of developers
> assuming that mixed-case means something outside of MS-land.  While
> SQL-Server does allow/display/use objects in mixed-case format, forcing
> that into Oracle *can* be done, but it's a bad idea.  The Oracle data
> dictionary stores object definitions upper-case, and allows those
> objects to be referenced in any mixed case.
> 
> Forcing object creation in Oracle as mixed-case is a DDL hack using ""
> around the object name.  At which point, the object can only be accessed
> in the exact case it was created enclosed with "".  For example
> 
> SQL> create table "Test" (id number);
> 
> SQL> desc test
> ERROR:
> ORA-04043: object "test" does not exist
> 
> SQL> desc "test"
> ERROR:
> ORA-04043: object "test" does not exist
> 
> SQL> desc Test
> ERROR:
> ORA-04043: object "test" does not exist
> 
> SQL desc "Test"
> Name   Null?Type
> -  ---
> ID NUMBER
> 
> > [EMAIL PROTECTED] wrote:
> >
> > Guys,
> >
> > Please help.  I work in an organization where we have both SQL Server
> > on NT and Oracle on Unix.  SQL Server and developers who are used to
> > GUI's in NT like column names to have mixed case with no underscores.
> > The Unix folk - like myself prefer underscores and one case.  Is there
> > any reason not to adopt mixed case for Oracle?  Is this really just
> > what I am used to?  I have been using this standard for so long that
> > it maybe the reasons I adopted it do not any longer exist or are not
> > as compelling as developer's today are more comfortable with mixed
> > case.
> >
> > Help!
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Suzy Vordos
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: STEVE OLLIG
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: naming conventions for Oracle/Unix vs. SQL Server

[STEVE OLLIG]

Suzy - it isn't just MS_LAND that uses mixed-case.  i've seen more than one
non-M$ shop take advantage of that in their namingConvetions.  shell
scripts, perl, java, and even other non-M$ databases - Sybase on HP-UX for
example.

i do however agree with the rest of the posts - probably not a good idea in
oracleLand ;)

funny how passionate some can be about small things like this.  we had a
rather heated debate on whether this:

try {
  stuff();
  more.stuff();
  }

or this:

try
{
  stuff();
  more.stuff();
}

was to be in our java standards recently.  FWIW, the former won out.

-Original Message-
Sent: Monday, July 29, 2002 12:39 PM
To: Multiple recipients of list ORACLE-L



Definately underscores, if simply just to break the habit of developers
assuming that mixed-case means something outside of MS-land.  While
SQL-Server does allow/display/use objects in mixed-case format, forcing
that into Oracle *can* be done, but it's a bad idea.  The Oracle data
dictionary stores object definitions upper-case, and allows those
objects to be referenced in any mixed case.

Forcing object creation in Oracle as mixed-case is a DDL hack using ""
around the object name.  At which point, the object can only be accessed
in the exact case it was created enclosed with "".  For example

SQL> create table "Test" (id number);

SQL> desc test
ERROR: 
ORA-04043: object "test" does not exist

SQL> desc "test"
ERROR: 
ORA-04043: object "test" does not exist

SQL> desc Test
ERROR: 
ORA-04043: object "test" does not exist

SQL desc "Test"
Name   Null?Type
-  ---
ID NUMBER


> [EMAIL PROTECTED] wrote:
> 
> Guys,
> 
> Please help.  I work in an organization where we have both SQL Server
> on NT and Oracle on Unix.  SQL Server and developers who are used to
> GUI's in NT like column names to have mixed case with no underscores.
> The Unix folk - like myself prefer underscores and one case.  Is there
> any reason not to adopt mixed case for Oracle?  Is this really just
> what I am used to?  I have been using this standard for so long that
> it maybe the reasons I adopted it do not any longer exist or are not
> as compelling as developer's today are more comfortable with mixed
> case.
> 
> Help!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: STEVE OLLIG
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: naming conventions for Oracle/Unix vs. SQL Server

[Jacques Kilchoer]

Title: RE: naming conventions for Oracle/Unix vs. SQL Server





>-Original Message-
>From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
>Please help.  I work in an organization where we have both
> SQL Server on NT and Oracle on Unix.  SQL Server and
> developers who are used to GUI's in NT like column names
> to have mixed case with no underscores.  The Unix folk -
> like myself prefer underscores and one case.  Is there
> any reason not to adopt mixed case for Oracle?  Is this really
> just what I am used to?  I have been using this standard for so
> long that it maybe the reasons I adopted it do not any longer
> exist or are not as compelling as developer's today are
> more comfortable with mixed case. Help!


While it is possible to create table names that are reserved words or contain "special" characters when using double quotes around the names

e.g.
create table "TABLE" ("COLUMN" varchar2 (20)) ;
create table "joe's table" ("joe's data" varchar2 (20)) ;


 I would not recommend it for the following reasons:


a) As other persons have pointed out, it makes coding more difficult in that object names have to be surrounded with double quotes;

b) You will run into difficulties when a third-party application is not smart enough to put double quotes around the column names;

c) You will run into difficulties when you hit an Oracle bug relating to "non-standard" names. Two of them that I know of so far:

- Cannot use alter table move on an index-organized table with lowercase column names (in 8.0, 8.1, 9.0 and maybe 9.2) : see example below

- Cannot have the "including" column for an index-organized table in lowercase in 8.1 (strangely enough it works in 8.0 and 9.0): see example below

- Also, I was unable to successfully implement a security policy (dbms_rls) on a table when the security policy function name was lowercase (but perhaps with some ingenuity it could be made to work)


**'
alter table move on index-organized table with lowercase column name error:


SQL*Plus: Release 9.0.1.0.1 - Production on Me Jul 24 10:07:12 2002 
(c) Copyright 2001 Oracle Corporation. All rights reserved. 
Connecté à : 
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production 
With the Partitioning option 
JServer Release 9.0.1.1.1 - Production 
SQL> create table book 
2 ("book_id" number not null, 
3 book_title varchar2 (40), 
4 constraint book_pk primary key ("book_id") 
5 ) 
6 organization index 
7 tablespace users ; 
Table créée. 


SQL> alter table book move tablespace tools ; 
alter table book move tablespace tools 
* 
ERREUR à la ligne 1 : 
ORA-00904: Nom de colonne non valide


**'
alter table move on index-organized table with lowercase column name error:
SQL*Plus: Release 8.1.7.0.0 - Production on Lu Jul 29 10:59:06 2002
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connecté à :
Oracle8i Enterprise Edition Release 8.1.7.2.1 - Production
With the Partitioning option
JServer Release 8.1.7.2.1 - Production
SQL> create table my_table
  2 (id number (7) primary key,
  3  name varchar2 (20),
  4  "type" char(1) not null,
  5  purchase date
  6 )
  7    organization index
  8    including "type"
  9    overflow tablespace users ;
create table my_table
*
ERREUR à la ligne 1 :
ORA-25184: nom de colonne attendu



SQL> -- syntax correct when removing double quotes around including column name
SQL> create table my_table
  2 (id number (7) primary key,
  3  name varchar2 (20),
  4  type char(1) not null,
  5  purchase date
  6 )
  7    organization index
  8    including type
  9    overflow tablespace users ;
Table créée.

Re: naming conventions for Oracle/Unix vs. SQL Server

[Suzy Vordos]

Definately underscores, if simply just to break the habit of developers
assuming that mixed-case means something outside of MS-land.  While
SQL-Server does allow/display/use objects in mixed-case format, forcing
that into Oracle *can* be done, but it's a bad idea.  The Oracle data
dictionary stores object definitions upper-case, and allows those
objects to be referenced in any mixed case.

Forcing object creation in Oracle as mixed-case is a DDL hack using ""
around the object name.  At which point, the object can only be accessed
in the exact case it was created enclosed with "".  For example

SQL> create table "Test" (id number);

SQL> desc test
ERROR: 
ORA-04043: object "test" does not exist

SQL> desc "test"
ERROR: 
ORA-04043: object "test" does not exist

SQL> desc Test
ERROR: 
ORA-04043: object "test" does not exist

SQL desc "Test"
Name   Null?Type
-  ---
ID NUMBER


> [EMAIL PROTECTED] wrote:
> 
> Guys,
> 
> Please help.  I work in an organization where we have both SQL Server
> on NT and Oracle on Unix.  SQL Server and developers who are used to
> GUI's in NT like column names to have mixed case with no underscores.
> The Unix folk - like myself prefer underscores and one case.  Is there
> any reason not to adopt mixed case for Oracle?  Is this really just
> what I am used to?  I have been using this standard for so long that
> it maybe the reasons I adopted it do not any longer exist or are not
> as compelling as developer's today are more comfortable with mixed
> case.
> 
> Help!
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suzy Vordos
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: naming conventions for Oracle/Unix vs. SQL Server

[Karniotis, Stephen]

Title: RE: naming conventions for Oracle/Unix vs. SQL Server









Paula:

 

  The mixed case for Oracle does not matter,
as Oracle is not case sensitive.  The
column names are stored in uppercase within the data dictionary.  However, for SQL Server, the case sensitivity
of column names is crucial.  

 

  Another black eye for MSFT.

 

Thank
You

 

Stephen
P. Karniotis

Product Architect

Compuware Corporation

Direct:   (248)
865-4350

Mobile:  (248)
408-2918

Email:  [EMAIL PROTECTED]

Web:    www.compuware.com

 

-Original
Message-
From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 29, 2002 11:59
AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: naming conventions
for Oracle/Unix vs. SQL Server

 

Guys, 

Please help.  I work in an
organization where we have both SQL Server on NT and Oracle on Unix.  SQL
Server and developers who are used to GUI's in NT like column names to have
mixed case with no underscores.  The Unix folk - like myself prefer
underscores and one case.  Is there any reason not to adopt mixed case for
Oracle?  Is this really just what I am used to?  I have been using
this standard for so long that it maybe the reasons I adopted it do not any longer
exist or are not as compelling as developer's today are more comfortable with
mixed case.  

Help! 










The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. 

RE: naming conventions for Oracle/Unix vs. SQL Server

[Mark Leith]

The only way to insert a column name in mixed case with Oracle is to enclose
the create statements column names in "double-quotes".. This also means that
when selecting from the created table, you have to enclose the mixed case
column name in double quotes as well.. Ad-hoc type queries could go wrong
all the time if people forget to do this..

There may be better reasons not to - but this is the one that jumps to mind
for me..

HTH

Mark

===
 Mark Leith | T: +44 (0)1905 330 281
 Sales & Marketing  | F: +44 (0)870 127 5283
 Cool Tools UK Ltd  | E: [EMAIL PROTECTED]
===
   http://www.cool-tools.co.uk
   Maximising throughput & performance

-Original Message-
[EMAIL PROTECTED]
Sent: 29 July 2002 16:59
To: Multiple recipients of list ORACLE-L


Guys,
Please help.  I work in an organization where we have both SQL Server on NT
and Oracle on Unix.  SQL Server and developers who are used to GUI's in NT
like column names to have mixed case with no underscores.  The Unix folk -
like myself prefer underscores and one case.  Is there any reason not to
adopt mixed case for Oracle?  Is this really just what I am used to?  I have
been using this standard for so long that it maybe the reasons I adopted it
do not any longer exist or are not as compelling as developer's today are
more comfortable with mixed case.
Help!

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Leith
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: naming conventions for Oracle/Unix vs. SQL Server

[Mercadante, Thomas F]

Title: RE: naming conventions for Oracle/Unix vs. SQL Server



Paula,
 
Someone will correct me if I'm wrong, but if you create tables and 
columns with mixed case within Oracle, then your developers will need to refer 
them surrounded by double quotes:  like:
 
SQL> create table "NameTable"("FirstName" 
varchar2(10));
 
Table 
created.
 
SQL> descr "NameTablee"ERROR:ORA-04043: object "NameTablee" 
does not exist
 
SQL> descr 
"NameTable" Name  
Null?    Type - 
 
--- FirstName  
VARCHAR2(10)
 
SQL> select firstname from 
"NameTable";select firstname from 
"NameTable"   *ERROR at line 
1:ORA-00904: invalid column name
 
SQL> select "FirstName" from 
"NameTable";
 
no rows selected
 
SQL> 
 
 
Really Sucks!
 
Tom Mercadante Oracle Certified Professional 

  -Original Message-From: 
  [EMAIL PROTECTED] 
  [mailto:[EMAIL PROTECTED]]Sent: Monday, July 29, 2002 
  11:59 AMTo: Multiple recipients of list ORACLE-LSubject: 
  RE: naming conventions for Oracle/Unix vs. SQL Server
  Guys, 
  Please help.  I work in an organization where we have 
  both SQL Server on NT and Oracle on Unix.  SQL Server and developers who 
  are used to GUI's in NT like column names to have mixed case with no 
  underscores.  The Unix folk - like myself prefer underscores and one 
  case.  Is there any reason not to adopt mixed case for Oracle?  Is 
  this really just what I am used to?  I have been using this standard for 
  so long that it maybe the reasons I adopted it do not any longer exist or are 
  not as compelling as developer's today are more comfortable with mixed 
  case.  
  Help! 

RE: naming conventions for Oracle/Unix vs. SQL Server

[Paula_Stankus]

Title: RE:  naming conventions for Oracle/Unix vs. SQL Server





Guys,


Please help.  I work in an organization where we have both SQL Server on NT and Oracle on Unix.  SQL Server and developers who are used to GUI's in NT like column names to have mixed case with no underscores.  The Unix folk - like myself prefer underscores and one case.  Is there any reason not to adopt mixed case for Oracle?  Is this really just what I am used to?  I have been using this standard for so long that it maybe the reasons I adopted it do not any longer exist or are not as compelling as developer's today are more comfortable with mixed case.  

Help!