With MySQL, how can I calculate a view field basing on a semi-related record?

I'm sorry for the vague title, but what I want to do can hardly be described with a one-liner.

I have a simple web app with a MySQL 5.1.30 backend to help me manage my finances. It's got a bill table:

id INT PRIMARY KEY,
date DATE

And a bill_rows table:

id INT,
bill INT REFERENCES bills (ID),
cost DECIMAL(16,2),
taxable TINYINT(1)

And finally, a taxed_bill_rows view, where an additional taxes column is introduced:

SELECT
    r.id AS `id`,
    r.bill AS `bill`,
    r.cost AS `cost`,
    (CASE
        WHEN i.taxable
            THEN floor((r.cost * 1.05 * 1.075) * 100) / 100.00 - r.cost)
        ELSE
            0.00
    END) AS `taxes`
FROM bill_rows r

The 1.05 factor is the federal tax rate, and the 1.075 is the provincial tax rate. (The * 1.05 * 1.075 is not a mistake: the provincial tax is applied over the federal tax too.)

However, these values aren't up-to开发者_如何学JAVA-date anymore (I'm actually very late on this). Since January 1^st of this year, the federal tax rate is 1% higher, up to 1.085.

To solve the tax rate changes problem, I've created a new tax_rates table:

ID INT,
date DATE,
federal DECIMAL(4,2),
provincial DECIMAL(4,2)

Each time the tax rates change, I will insert a new record there.

Now, the problem is, how can I access the relevant tax rates, based on the date field of a bill, from my view? I can't just do LEFT JOIN tax_rates tr ON tr.date < b.date as that can potentially bring back too many records. Adding a tax_rates foreign key to the bills table would do it, but the changes are so occasional that it seems barely worth it.

Your tax_rates table needs two date columns to indicate the start & end:

CREATE TABLE tax_rates (
  id INT,
  start_date DATE,
  end_date DATE,
  federal_tax_rate DECIMAL(4,2),
  provincial_tax_rate DECIMAL(4,2))

Then, you can use BETWEEN to JOIN:

JOIN tax_rates tr ON tr.date BETWEEN b.start_date AND b.end_date

foregn key for tax rate will be the best solution.

best for performance and best for maintenance,

any other solution will be slower and less readable

i.e you can add fields start_date and end_date to tax_rates table and to do join like

left join tax_rates tr on b.`date` between tr.start_date and tr.end_date

in this case you also should keep 0 at start_date for first record and large date for last record