如何在sql数据库中正确实现商业数据关系？ [英] How can I properly implement commerce data relationships in a sql database?
问题描述
这里是我目前的架构，我会放一个？
密钥：
table_name
----------
col1 | col2 | col3
tax_zone
---------
tax_zone_id | tax_rate |描述
sales_order
-----------
sales_order_id | tax_zone_id（FK）或tax_rate（？）
sales_order_item
-----------
sales_order_item_id | sales_order_id（FK）| sell_price | amount_tax或tax_rate（？）
所以，如果还不清楚，这个困境是不是我应该将税单数据存储在订单的单独行中，或者使用联接来拉取tax_zone信息，然后在我的查询中执行某些操作，如（tz.tax_rate * so.order_amount）as order_total 。
目前，我正在考虑使用刚刚描述的方法。我用这种方法看到了一个问题，但我似乎无法弄清楚如何补救。特定区域的税率可能会发生变化。这意味着如果某个区域的税率发生变化，并且我使用的是外键引用，那么速率的变化将反映在以不同速率完成的过去订单中。这导致一个问题，因为目前我正在使用此表中的数据来存储已处理的订单和仍然打开的订单，因此如果有人要重新打印过去的订单，则订单的总金额将会发生变化。
我存储具体税率或税额的问题在于每次有人要修改订单时，我必须更新该行再次与这些价值观的变化。
在写这个过程中，我开始转向后一种想法，这两个更好。
也许如果有人可以为我提供以下问题的答案，我可以再多研究一次。
这是数据库建模中的一个已知问题吗？
有哪些知名的权威机构已经出版了一本书/文章？
任何帮助都非常感激，谢谢！
嗯，版本控制和历史是数据库建模中一个众所周知的问题。您的解决方案很常见。
对于像VAT-rate这样的简单枚举，简单的外键tax_id引用税表（id）将会执行。税务代表不应该更新，一旦tax_id被采纳，它应该永远在那里。如果年底税率发生变化，即使具有新价值的记录已存在，新记录应纳入税表
搜索引擎的最佳搜索短语可能是时间数据库。
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".
这篇关于如何在sql数据库中正确实现商业数据关系？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
