Database Design regarding applicable dates

Question

Suppose I have a products table. Some products have a fee associated with them. Those fees can change from time to time, but I need to know what that fee is for any given time.

My initial design included:

| products | 
|----------|
| id       |
| name     |

| fees           |
|----------------|
| id             |
| amount         |
| effective_from |
| effective_to   |
| product_id     |

Sample Fees table:

| id | amount | effective_from | effective_to | product_id |
|----|--------|----------------|--------------|------------|
| 1  | 20     | 2015-01-01     | 2015-06-01   | 1          |
| 2  | 25     | 2015-06-01     | 2015-09-01   | 1          |
| 3  | 27     | 2015-09-01     | NULL         | 1          |

Benefits:

• Visually obvious which fee applies for a given date

• Simple Query to fetch applicable fee:

  WHERE $date < effective_to AND $date > effective_from;

Downsides:

• Duplicate data: row 1 effective_to = row 2 effective_from
• Ambiguous if 2015-09-01 applies to row 2 or row 3. (code would determine)
• Fetching the applicable fee doesn't work for the active fee because of the Null value.
• Data can become corrupted if row 1 effective_to != row 2 effective_from

Alternative design:

| fees           |
|----------------|
| id             |
| amount         |
| effective_from |
| product_id     |

Sample Fees table:

| id | amount | effective_from | product_id |
|----|--------|----------------|------------|
| 1  | 20     | 2015-01-01     | 1          |
| 2  | 25     | 2015-06-01     | 1          |
| 3  | 27     | 2015-09-01     | 1          |

Benefits:

• Normalized data (no duplicate info)
• Robust to gaps without a fee
• No Null value

• Still easy to query:

  WHERE effective_from < $date ORDER BY effective_from DESC LIMIT 1;

Downsides:

• If data was entered wrong, we wouldn't necessarily know it
• It isn't as visually obvious which fee applies

Which should I use? Does anyone have a better suggestion? Thanks for sticking with me!

Answer 1

Given INDEX(effective_from), the second design will be much more performant because it needs to touch only the one row. The first design needs to scan, on average, half the table.

Answer 2

Have you benchmarked the performance of any queries that find the appropriate row based only on the effective_from date?

I think you'll find that they are not as inefficient in comparison to the other as you might guess, as the best optimisation for either one is to scan an index on id and effective_from.

I would always go for the second design -- databases are better at querying than they are at enforcing intra-row constraints.