Even when our table layout respects the rules, we can still refine it by looking at the following additional issues.
In Chapter 3 (section Data that are Results), we saw that we could avoid reserving a column for the tax amount, provided we have the exact tax rate in a reference table. However this rate could change so we need a more complete table that contains date ranges and the corresponding rate. This way, projecting the system over the time dimension, we can ensure that it will accommodate rate fluctuations. Note that the following sale table is not complete:
|
table: sale |
column name |
sample value |
|---|---|---|
|
*date |
2006-03-17 | |
|
*internal_id |
123 | |
|
condition_code |
N |
|
table: condition |
column name |
sample value |
|---|---|---|
|
*code |
N | |
|
description |
New |
Comparing the date column from the sale table with the start_date and end_date from the following tax_rate table, we can find the exact tax rate for the date of sale:
|
table: tax_rate |
column name |
sample value |
|---|---|---|
| ... |