问题描述
示例:用户可以一次提交发票和所有引用(客户地址,计算金额,产品描述)这个发票包含的和计算应该随着时间的推移而存储。
我们需要保持参考,但如果eg产品名称更改?所以我们需要复制一切,以备日后记录,不受未来变化的影响。即使产品被删除,他们需要在发票存储后稍后进行审核。
有关数据库设计的最佳做法是什么?即使是最灵活的方法,例如当用户要稍后编辑他的发票,并从db中恢复它时?
谢谢!
这是一种方法:
本质上,我们从不修改或删除现有数据。我们通过创建一个新版本修改它。我们通过设置DELETED标志来删除。
例如:
- 如果产品更改价格,我们会在PRODUCT_VERSION中插入新行,而旧订单与旧的PRODUCT_VERSION和旧价格保持联系。
- 当买家更改地址时,我们只需插入一个在CUSTOMER_VERSION的新行,并链接新的订单,同时保持旧的订单链接到旧版本。
- 如果产品被删除,我们不会真正删除它 - 我们只是设置PRODUCT.DELETED标志,所有历史上针对该产品的订单都保留在数据库中。
- 如果客户被删除(例如,因为他被要求被注销),请设置客户.DELETED标志。
注意事项:
- 如果产品名称必须是唯一的,那么在上面的模型中不能以声明方式强制执行。您将需要将NAME从PRODUCT_VERSION推广到PRODUCT,使其成为关键,并放弃演进产品名称的能力,或者仅通过最新的PRODUCT_VER(可能通过触发器)强制执行唯一性。
- 客户的隐私存在潜在问题。如果客户从系统中删除,可能需要从数据库中物理删除其数据,只需设置CUSTOMER.DELETED就不会这样做。如果这是一个问题,请勿在所有客户版本中隐藏隐私敏感数据,或者将现有订单从真实客户中断开,并将其重新连接到特殊的匿名客户,然后实际删除所有客户版本。 li>
此模型使用了大量的识别关系。这导致胖的外键,可能是一个存储问题,因为MySQL不支持前沿索引压缩(不像Oracle),但另一方面,并且此聚类可以有利于性能。此外,JOINs不太必要。
具有非识别关系和代理键的等效模型将如下所示:
We have a requirement in our application where we need to store references for later access.
Example: A user can commit an invoice at a time and all references(customer address, calculated amount of money, product descriptions) which this invoice contains and calculations should be stored over time.
We need to hold the references somehow but what if the e.g. the product name changes? So somehow we need to copy everything so its documented for later and not affected by changes in future. Even when products are deleted, they need to reviewed later when the invoice is stored.
What is the best practise here regarding database design? Even what is the most flexible approach e.g. when the user want to edit his invoice later and restore it from the db?
Thank you!
Here is one way to do it:
Essentially, we never modify or delete the existing data. We "modify" it by creating a new version. We "delete" it by setting the DELETED flag.
For example:
- If product changes the price, we insert a new row into PRODUCT_VERSION while old orders are kept connected to the old PRODUCT_VERSION and the old price.
- When buyer changes the address, we simply insert a new row in CUSTOMER_VERSION and link new orders to that, while keeping the old orders linked to the old version.
- If product is deleted, we don't really delete it - we simply set the PRODUCT.DELETED flag, so all the orders historically made for that product stay in the database.
- If customer is deleted (e.g. because (s)he requested to be unregistered), set the CUSTOMER.DELETED flag.
Caveats:
- If product name needs to be unique, that can't be enforced declaratively in the model above. You'll either need to "promote" the NAME from PRODUCT_VERSION to PRODUCT, make it a key there and give-up ability to "evolve" product's name, or enforce uniqueness on only latest PRODUCT_VER (probably through triggers).
- There is a potential problem with the customer's privacy. If a customer is deleted from the system, it may be desirable to physically remove its data from the database and just setting CUSTOMER.DELETED won't do that. If that's a concern, either blank-out the privacy-sensitive data in all the customer's versions, or alternatively disconnect existing orders from the real customer and reconnect them to a special "anonymous" customer, then physically delete all the customer versions.
This model uses a lot of identifying relationships. This leads to "fat" foreign keys and could be a bit of a storage problem since MySQL doesn't support leading-edge index compression (unlike, say, Oracle), but on the other hand InnoDB always clusters the data on PK and this clustering can be beneficial for performance. Also, JOINs are less necessary.
Equivalent model with non-identifying relationships and surrogate keys would look like this:
这篇关于数据库历史的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!