Re: Naming Convention for Columns

On Fri, 06 Mar 1998 21:43:26 -0500, Jack Kangas <jkangas_at_iwaynet.net> wrote:

>Oh, I can resist a religious battle
>
>Seriously, I would like to toss out some of our "arrived" at standards
>(and we have lived thru 2 generations now). What follows are bullets of
>what we do and I tried to explain some of it...
>
>-I also vote for the SAME NAME in all tables... (where a PK/FK
>relationship exists)
>-I also use 'table qualified' name for the PRIMARY key ONLY (i.e.
>Bank.BankKey, Customer.CustomerKey). The only quasi-exception is where
>there are TWO or more relationships between two tables (happens once
>every 3rd ERM/project) - when that happens the relationship 'meaning'
>adds to the qualification of the name.
>-nonkey attributes can exist without the table name... and YES, we have
>several attributes called "NAME" (Bank.name, Customer.Name, etc...).
>What also happens that when someone references a non key attribute, the
>table name is USUALLY required to identify the attriubte, so why repeat
>it.
>-Notice in the above examples, we spell out the names. Now that we are
>away from the 18 byte limit (given SQLServer) and yes, I jeopordize my
>backward compatibility with DB2, but...... (isn't DB2 getting to that
>point soon anyway?). Our rule is "simple, but readable" - we also
>assume ANY user (by definition) has a basic understanding of the
>logic/data (this doesn't include any complexities - just what planet
>they are looking at)
>-We use capital letters to delinate major words (see examples above)
>-We still use the OLD concept of TYPING the attributes (Text, Date,
>Time, Number, Percent, Volume, Code, etc) and have exceptions (like SSN,
>Name, etc (a.k.a. user defined data types))
>
>We had several major points that lured us away from our old
>standards...(that you may or may not want to consider)
>1. End Users (make it easy to understand and obvious as HECK)
>2. PC reporting tool (end users) and code generators, no one types
>anymore ( it is 'point and click' or 'cut and paste'), relationships are
>click and drag (remember "same name"). Think of how the end users will
>see and use the data (not just the programmers.... no matter how hard
>they whine (yes I am a DBA))
>3. NOTHING exists in a vacuum. Attributes, tables, constriants, user
>defined data types... they all exist in relationship to one another...
>in the system catalog. Not that we query that much ... we use the GUI
>tools to show us everything. In other words why

I also use the field names where contents are the same regardless of how many tables contain that field.

While the programmer at work disagrees with me, I always reference the table name somehow in the column name. My opinion is that any column name should stand on it's own. In other words, columns should be identifiable without being qualified by the table name. E.g. customer.cust_name, customer.cust_desc. This helps me in writing queries and talking about the data with other consultants.

I always use types as well. My convention is: ID = RDBMS generated unique identifier. NUM = number that is not generated by the RDBMS. Could be generated by external systems or programs
COUNT = contains a count of some entity
TYPE = a category of some sort
CODE = a short identifier for some entity with a longer name. E.g. company_code = AMEX
NAME = the proper name for an entity. E.g. company_name = 'American Express'
DESC = a description of an entity or code. E.g. error_desc = 'list index out of bounds'
Using these conventions is especially helpful when revisiting old models in discussion with others where they have become familiar with your conventions.

I always try to used datatypes consistently. In the case of string or character fields I use only char(10), varchar(20), varchar(40), varchar(80), varchar(120) except in cases where the universe of values is of consistent length or the length is known specifically. E.g. - codes, account numbers, or other formatted types.

hope this helps,
Jason McElroy

 Jason McElroy 	Project Director	<jmcelroy_at_inch.com>		
 Paperless Office Enterprises			P: 212.883.6600
 420 Lexington Ave. Ste 215			F: 212.883.6604
 NYC 10170					W: www.poe-ny.com
********************************************************************