15.00 - Column Naming Convention - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Column Naming Convention

Teradata recommends the following syntax for naming columns:

where:

 

Syntax element …

Specifies …

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

    billin_gorddate

    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 SQL Data Definition Language and SQL External Routine Programming 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.