Advice on schema for multiple tax rates on order management system

[ML!]

This seems kind of simple but I'm having trouble wrapping my head around it and haven't found exactly what I'm looking for.

I need some help on a schema for an order management system that considers...

* multiple sales tax rates on a product
* Tax_2 might be compounded after Tax_1 is applied...or it may not
* Taxes may only apply in certain tax 'zones' (by country and/or state/province)
* Certain purchasers may be tax exempt (not only based on zone)

I just need to demonstrate this (not build it) and I'd be happy to figure out how to apply multiple tax rates to one product, presumably through one tax table.

I've attached my attempt at mapping this out. Note the tables aren't fully fleshed out in terms of the fields (therefore the ellipses). The primary and foreign keys are implied by the lines.

Thanks in advance for your kind input.

ML!

ML!

 

[Uncle Gizmo]

Can I assume all taxes will be a percentage?

In your products table you have listed: Tax 1, Tax 2, .... This is usually a good indication of incorrect structure, the tax rates are a good candidate for a separate table. Separate table something like:

ProductID, TaxID,
1, 3,
1,15
23, 3
23, 15
23, 30

And possibly a further table to store your tax rates. May or may not be necessary.

 

[ML!]

That's exactly what I needed. I knew the way I setup the product table wasn't properly normalized but sleepy brain couldn't get to the correct structure. Thanks Uncle G