SQL Hacks

Chapter 7. Organizing Data

When representing complex data you have lots of choices to make. For any given problem there will be a variety of different schemas that you can use. The choices you make will impact three factors: performance, complexity, and redundancy. It would be nice to be able to optimize all three of these factors, but just as with “fast, cheap, and reliable,” you will generally need to “pick any two.”

Keep Track of Infrequently Changing Values

It may be enough for your database to keep track of current real-world information. But sometimes you need to record historical data as well.

If you’re calculating values for reporting on the current state of things, the most up-to-date information is sufficient. But if your reports span a significant time period, such as a year-over-year comparison, you will need to take changes into account. This hack looks at two options for storing current and historical data in the same table.

Suppose you’re keeping track of products and their prices. The most direct and data-oriented way to ensure that you can always quickly find the prices of items is to have a table of prices with a composite primary key of the product name and the date. Then, every day, you need to “confirm” the price of each item by adding a new row to the database, even when the price doesn’t change. This approach is safe and reliable, leaving an audit trail of prices, and you can create queries using the current date to find the current prices. Your table would look ...