Archives of the TeradataForum

Message Posted: Tue, 02 Jul 2002 @ 12:26:55 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Here's another two cents on column names. This subject is near and dear to my heart.

After normalization, I think that naming standards and especially the use of a single business name to denote the same data element or column across all tables and databases is the second most critical item for building a successful enterprise data warehouse. Table name conventions are likewise just as critical.

Naming conventions are the domain of the DBA and not the customer/user. They are fundamental and a function of table design. That does not mean that the customer should be ignored because the customer often is the most knowledgeable about the nuances of any data element.

Naming should not be just some kind of "tag name" to facilitate processing, or because that is what one individual desires, or because that is what it is called on the mainframe or another source. On the mainframe, just about any tag will do since most internal record names are buried under forms/screens that are more friendly than the internal record names.

Naming also means a consistent data definition across all tables. For example, our school location numbers are defined as LOC Smallint Format 'Z999' in several hundred instances without variation. Once you encounter this column in one table, it's the same everywhere and so enhances the learning process.

Unlike the mainframe, in the relational world, the underlying column name may be carried directly over to the user level without a form/screen in front to hide poorly named columns. That means that the DBA better be somewhat of an etymologist. Good naming also means less recourse to metadata look-ups.

So much for Motherhood and Apple Pie.

Yup, standardization/naming conventions causes the DBA to get lumps, flaming executive e-mails, confrontations with furious data "owners", questions about his sanity, "other opportunity" inquiries, and being a bottleneck......until it gets accepted.....if the DBA can hold out, it eventually will get accepted because it works and enhances productivity.

One appeasement I have used is to add a comment on each column defining the source file name and source data element name. So in the unlikely event that someone needs to trace some data back to its source, it can be done readily using the HELP TABLE command

For example: Comment on Column ECE_Placement.Transaction_Date is 'ECE-Placement-Record.ECE-Place-Action-Date';. The ECE-Placement- Record is the mainframe record name and ECE-Place-Action-Date is the mainframe data element name.

In the last five (5) years, there has only been one or two occasions at our site where some obscure data had to be traced back to its source. So such column comments are more for appeasement then actual usage.

Another advantage of using one accurate business name across all tables is that views can maintain the same base table names and preclude another layer of mapping.

With almost 1,700 tables, every table in our production data base has a comment describing the table contents using words that are not in the table name itself.

Our objective and the bottom line is that naming standards/conventions should convey meaning and connotation so that when a customer looks at the table name, the column name, table/column comments, and the column data, there is little doubt about the data and what it represents.

Hope this helps.

John Balchunas
Jefferson County Public Schools

	<Prev	Next>		<<First	<Prev	Next>	Last>>