Re: Naming Convention for Columns

adelamerens@fisi-madison.com wrote: > In article <6dnbes$6r5@newsops.execpc.com>, > dave.mullen@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