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.
- 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
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. |
- 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.
- $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.