Column Names and Constraints | Database Design | Teradata Vantage - Column Names and Constraints - Advanced SQL Engine - Teradata Database

Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
qby1588121512748.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1094
lifecycle
previous
Product Category
Teradata Vantageā„¢

The topic of domains leads naturally to the topic of naming columns and assigning constraints to them.

Always use a consistent method to define the names of the columns in your databases.

The following set of columns illustrates an unsophisticated example of why it is important to name your columns carefully.

Case Study, Part 1

Suppose you have defined the following two entities:

PrimaryKeyColumn ColumnA Date Date 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
PrimaryKeyColumn Date Date Date
PK, SA      
  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

Can you tell if the highlighted dates in these tables all refer to the same point in time? Can you tell if any of them do? If not, then you should not compare them. But how do you know whether the dates are drawn from the same domain or not?

Case Study, Part 2

Now assume that you have identified and named all the domains in your database. The next step is to apply those domains to the identified entities in your logical model.

Consider the same two entities we examined before, only now their columns have been assigned names that represent the domains from which those column values are drawn.

employee
emp_num last_name birth_date hire_date termination_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_date shipping_date billing_date
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