Guidelines for Naming Columns | Database Design | Teradata Vantage - 17.10 - Guidelines for Naming Columns - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

As it has been defined so far, this column naming convention fails to define the Date domain with enough rigor to prevent an application developer from comparing a shipping date with a birth or hire date, but it provides a sufficient example for illustration as well as a first approximation to how you might define a domain rigorously.

Because ANSI/ISO SQL does not have facilities for defining domains with this level of rigor, your only course of action is to do so textually or to create appropriate distinct user-defined data types (see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL External Routine Programming, B035-1147 for details about how to create UDTs and their associated database objects). Be aware that you cannot specify constraints on UDT columns, so their usefulness for defining domains is less than it might otherwise be.

You can record these distinctions in several ways.
  • DBC.TVFields.CommentString

    Create this comment string using the COMMENT SQL statement.

    View the comment you create using the Columns system view.

  • Column naming conventions
  • Metadata repositories
  • ATM Domains form

After you have rigorously defined your domains textually, your next step should be to name your columns by qualifying those domain names.

Column Naming Convention

Teradata recommends the following syntax for naming columns:

qualifier domain_name
qualifier
A unique name to differentiate one use of a domain from all the other uses of that domain.
domain_name
The name of the domain being qualified.
Using this convention, you could define date domains such as the following example names:
  • HRDate
  • HR_Date
  • OrderDate
  • Order_Date

The more finely defined domain names naturally lead to tables having columns with names like those in the following tables.

employee
employee_number last_name birth_HR_date hire_HR_date term_HR_date
PK, SA        
    03 Apr 1960 17 Apr 1981 29 Nov 1987
    31 Jan 1937 03 Apr 1960 30 Jun 1981
    05 Mar 1930 21 Aug 1950 03 Apr 1960
order
order_number order_orddate shipping_orddate billing_orddate
PK, UA      
  03 Apr 1960 10 Apr 1960 13 Apr 1960
  29 Mar 1960 03 Apr 1960 06 Apr 1960
  24 Mar 1960 31 Mar 1960 03 Apr 1960

With this convention, an application programmer would know never to compare an employee birth date with an order shipping date because they do not represent the same thing even though they share the same set of valid values.

A more rigorous approach might be to create distinct user-defined data types on birth_HR_date, hire_HR_date, and term_HR_date, and then create methods that only permit intra-domain comparisons on those domains. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL External Routine Programming, B035-1147 for information about how to create UDTs and their associated database objects.

Note, too, that superficially “identical” values are not necessarily drawn from the same domain. The following dollar amounts, for example, do not refer to the same real world objects:
  • $1.00 USD
  • $1.00 Canadian
  • $1.00 Australian

In the physical design phase, you can further limit column values with constraints imposed on them using the CREATE TABLE or ALTER TABLE statements.

For example, the HR_Date domain would probably exclude all weekend and holiday dates, and if your company only ships goods on Fridays, then you would place a Fridays-only constraint on the shipping_orddate column.

Naming Foreign Key Columns

You should also name foreign key columns to match their names in their primary tables. For example, the foreign key columns in the following relation are not only indicated by the name of their primary table (order) but are also demarcated with the characters FK to redundantly indicate their origin in another table.

lineitem
lineitem_number order_FK_number order_FK_year_date
PK FK FK

Metadata Definitions

All domain definitions should be stored in a metadata database and those definitions should be made available to all users of the database.

ATM Domains Form

As part of the transition from the logical model of your database to its physical implementation, you should record all domains that you identify on a Domains form. For more information, see Domains Form.