Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Naming Convention for Columns

Re: Naming Convention for Columns

From: Art S. Kagel <kagel_at_bloomberg.com>
Date: 1998/03/06
Message-ID: <3500282C.3478@bloomberg.com>#1/1

adelamerens_at_fisi-madison.com wrote:

> In article <6dnbes$6r5_at_newsops.execpc.com>,
> dave.mullen_at_emjay.com (Dave Mullen) wrote:
 

> > We are setting up new database naming conventions, and I'd like to
> > hear other people's thoughts on what they consider to be important
> > considerations.
> >
> > In particular, I'd like to know how people feel about prefixing the
> > names of columns in a relational data base with a short prefix
> > representing the table to which the column belongs. (Foreign keys
> > would retain the prefix of the foreign table, [clip]
 

> Dave,
 

> It is very helpful to a developer (and anyone else that must refer to the
> items) to have standardized nomenclature for all data references. With
> respect to column naming conventions, prefixing the column name with an
> abbreviated table reference is precisely the way I have always approached
> this issue. Even foreign keys follow the rule... For example
 

> Customer Master Table Customer Detail Table
> cm_key cd_key
> cm_acctno cd_cmkey (foreign key to Customer Master)
 

> With long column names and plethora of tables, the problem is trying to keep
> the prefixes short enough not to be intrusive but long enough to be
> descriptive.
 

> A well-contrived nomenclature standard allows for a cleaner appearance when
> coding, obviates the need to use table names for the qualification of columns
> (i.e. eliminates redundance) [although some would argue, with validity, that
> all columns be fully qualified...] and is much easier for developers to
> remember without constant reference to table structures, etc...
 

> Well now, that should open a big ole can o' worms!!

Well I'm willing to jump into the can and get my toes slimmy!

My convention is very simple! If a column contains the same data, semantically, then it should have the same name in EVERY TABLE that contains it. This is true whether the column is a primary/foreign key part or some data that I've duplicated for performance reasons. In my scheme all column names uniquely identify a logical data value. The same column name in different tables name the same data and no other, unrelated, column can have that name.

So, in the example given above, I would name the Customer Master foreign key cm_key in the Customer Detail table also. This makes coding VERY clear and if I see a column in a table that does not fit the table's naming prefix (like the 'cm' & 'cd' prefixes above, which by the way I resist using though I support the sentiment and don't mind when others use such prefixes) I know that that column MUST either be a foreign key or a denormalized value from another table. When someone adds a new update program it is VERY obvious from the schema whether a column has been denormalized and may have to be replicated in another table. Also, admittedly, when combined with prefixing, it is even obvious which is the primary location of a denormalized column which is important for data audit and cleanup.

Art S. Kagel Received on Fri Mar 06 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US