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 =
10000" 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) = 10000". 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 <cary.millsap_at_hotsos.com> 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 <cary.millsap_at_hotsos.com> 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: gc22_at_lucent.com
> >
> > 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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
> >
> > 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: ListGuru_at_fatcity.com (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: wisernet100_at_yahoo.com
>
> 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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>
=== message truncated ===
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: wisernet100_at_yahoo.com
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: ListGuru_at_fatcity.com (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: cary.millsap_at_hotsos.com
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: ListGuru_at_fatcity.com (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: NDATFM_at_labor.state.ny.us
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: ListGuru_at_fatcity.com (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).
Received on Wed Jul 31 2002 - 08:13:19 CDT
