Improving the Structure

Even when our table layout respects the rules, we can still refine it by looking at the following additional issues.

Scalability over Time

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

Get Creating your MySQL Database: Practical Design Tips and Techniques now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.