An advantage of using non-text data domains for columns in tables is that your RDBMS can perform calculations
based on the contents of the columns.
For example, if you want to calculate the cost of an order where the customer had a 15% discount coupon, you can create a field to store the discount and multiply the total retail cost of the order by .85 (1 - .15).
The domains that enable you to perform calculations on their contents are
Many relational database management systems also will allow you to perform calculations on DATETIME columns.
Yes, it is possible to subtract one date from another and to calculate the number of days between them.
This is a handy ability when you want to determine, for example, the length of time between an order's placement and its shipment. If an order were placed on 03/07/00 and shipped on 03/21/00, the difference of 14 days
could indicate that the item ordered was out of stock and required a special order from the producer.
Pay careful attention to which data type you use in your calculations. For example, if you have an INT value of 35 and a DECIMAL value of 5.25, you can handle the result in two ways:
- As an INT, in which case the result would be 29 (29.75 with the decimal truncated)
- As a DECIMAL, in which case the result would be 29.75
Always consult your RDBMS's documentation to determine the system requirements for handling cross-domain calculations.
Every field in a base table must uniquely and independently store information about the table subject.
Because calculated fields derive their values from other fields in a base table, you should never include them in a base table. Instead, you should only put them in user views
described later in this course. User views specify which users are permitted access to specific data in a database. The next lesson describes null values
Before you move on to the next lesson, click the Exercise link reinforce your understanding of calculated domains.
Calculation Domain - Exercise