Is there a standard SQL Table design for overriding 'big picture' default values with lower level de
- by RichardHowells
Here's an example. Suppose we are trying to calculate a service charge.
Say sales in the USA attract a 10 dollar charge, sales in the UK attract a 20 dollar charge
So far it's easy - we are starting to imagine a table that lists charges by country.
Now lets assume that Alaska and Hawaii are treated as special cases they are both 15 dollars
That suggests a table with states, Alaska and Hawaii are charged at 15, but presumably we need 48 (redundant) rows all saying 10. This gives us a maintainance problem, our user only wants to type 10 once NOT 48 times. It does not sit well with the UK either. The UK does not have states.
Suppose we throw in another couple of cross cutting rules.
If you order by phone there is a 10% supplement on the charge.
If you order via the web there is a 10% discount.
But for some reason best known to the owners of the business the web/phone supplement/discount are not applied in Hawaii.
It seems to me that this is quite a common kind of problem and there is probably a well known arrangement of tables to store the data. Most cases get handled by broad brush answers, but there are some very detailed low level variations that give rise to a huge number of theoretical combinations, most of which are not used.
