如何在sql数据库中正确实现商业数据关系

如何在sql数据库中正确实现商业数据关系

本文介绍了如何在sql数据库中正确实现商业数据关系?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

首先,我想开始表达我不是想让某人为我创建我的表格。我花了一些时间来衡量我的设计中两种可能性之间的选择,我想在我去之前找到一些建议,并用我目前的想法进行运行。



这里是我目前的架构,我会放一个?



密钥:

  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".

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

这篇关于如何在sql数据库中正确实现商业数据关系?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-04 21:40