嗯，版本控制和历史是数据库建模中一个众所周知的问题。您的解决方案很常见。

对于像VAT-rate这样的简单枚举，简单的外键tax_id引用税表（id）将会执行。税务代表不应该更新，一旦tax_id被采纳，它应该永远在那里。如果年底税率发生变化，即使具有新价值的记录已存在，新记录应纳入税表

搜索引擎的***搜索短语可能是时间数据库。

更新：
http://www.google.nl/url?sa = t& source = web& cd = 2& ved = 0CCMQFjAB& url = http％3A％2F％2Fwww.faapartners.com％2Fdownloads％2Foverige-publicaties％2Fpresentatie-over-tijd-in-databases％2Fat_download％2Ffile& = j& q = veldwijk％20temporal& ei = HQdxTtimCcKr-QansM28CQ& usg = AFQjCNEg9puU8WR1KIm90voSDp13WmE0-g& cad = rja

First, I'd like to start out expressing that I am not trying to just have someone create my table schema for me. I have spent some time weighing the options between the two possibilities in my design and I wanted to get some advice before I go and run wild with my current idea.

Here is my current schema, I will put a ? next to columns I'm considering using.

Key:

table_name
----------
col1 | col2 | col3

tax_zone
---------
tax_zone_id | tax_rate | description

sales_order
-----------
sales_order_id | tax_zone_id (FK) or tax_rate (?)

sales_order_item
-----------
sales_order_item_id | sales_order_id (FK) | selling_price | amount_tax or tax_rate (?)

So, if it wasn't already clear, the dilemma is whether or not I should store the tax data in the individual rows for an order, or use a join to pull the tax_zone information and then do something in my query like (tz.tax_rate * so.order_amount) as order_total.

At present, I was thinking of using the method I just described. There is a problem I see with this methodology though that I can't seem to figure out how to remedy. Tax rates for specific zones are subject to change. This means that if a tax rate changes for a zone and I'm using a foreign key reference, the change in the rate will reflect in past orders that were done with a different rate. This causes an issue because at present I'm using the data in this table to store both orders that have been processed and orders that are still open, therefore if someone were to go re-print a past order, the total amount for the order will have changed.

My problem with storing the specific rate or tax amount is that it means every time someone was going to edit an order, I would have to update that row again with the changes to those values.

In the process of writing this, I'm starting to move towards the latter idea being the better of the two.

Perhaps if someone can just provide me the answer to the following questions so I can go research them myself some more.

Is this a known problem in database modeling? Are there any well known "authorities" on the subject that have published a book / article?

Any help is much appreciated, thanks!

Well, versioning and history is a well known problem in database modelling. Your solution is very common.

For a simple enumeration like VAT-rates a simple "foreign key tax_id referencing taxtable(id)" will do. The tax-table should never be updated, once a tax_id is enterered, it should stay there forever. If the tax rates are changed at the end of the year, new record should be entered into the tax_table even if records with the new value already exist.

The best search phrase for search engines is probably "temporal database".

UPDATE: http://www.google.nl/url?sa=t&source=web&cd=2&ved=0CCMQFjAB&url=http%3A%2F%2Fwww.faapartners.com%2Fdownloads%2Foverige-publicaties%2Fpresentatie-over-tijd-in-databases%2Fat_download%2Ffile&rct=j&q=veldwijk%20temporal&ei=HQdxTtimCcKr-QansM28CQ&usg=AFQjCNEg9puU8WR1KIm90voSDp13WmE0-g&cad=rja