以有效的方式存储关系对象的修订 [英] Storing Revisions of Relational Objects in an Efficient Way

问题描述

我不知道这种类型的问题是否得到了回应。在我的数据库中我有一个产品表和规格表。每个产品都可以有多种规格。在这里，我需要将每个产品的修订版存储在数据库中，以便稍后查询它们以用于历史记录。

I'm not sure if this type of question has been answered before. In my database I have a product table and specifications table. Each product can have multiple specifications. Here I need to store the revisions of each product in database in order to query them later on for history purposes.

所以我需要一种有效的方法来在每次用户改变这些关系时将产品的关系存储到规格中。数据量也可以变得非常大。例如，假设数据库中有10万个产品：每个产品可以有30个规格，每个产品最少有20个修订版本。因此，通过将所有数据存储在单个表中，数据量变得非常高。

So I need an efficient way to store the products' relations to specifications each time users make changes to these relations. Also the amount of data can become very big. For example, suppose there are 100000 products in database: each product can have 30 specifications and also there are minimum of 20 revisions on each product. So by storing all the data in a single table the amount of data becomes enormously high.

任何建议？

推荐答案

如果这仅仅是为了归档的目的，那么修改的单独的表可能更好。

If this is purely for 'archival' purposes then maybe a separate table for the revisions is better.

但是，如果你需要将以前的修订版本等同于当前的修订版本（例如，如果要让用户将产品还原到以前的版本），那么最好保留单个产品表，而不是在表之间复制数据。如果您担心性能，这是索引。

However if you need to treat previous revisions equally to current revisions (for example, if you want to give users the ability to revert a product to a previous revision), then it is probably best to keep a single products table, rather than copying data between tables. If you are worried about performance, this is what indexes are for.

您可以为产品表创建复合主键，例如。 PRIMARY KEY（product_id，revision）。可能是存储过程以查找当前修订版本;通过为特定的 product_id &mdash选择最高修订版本将有用。

You can create a compound primary key for the product table, e.g. PRIMARY KEY (product_id, revision). Maybe a stored proc to find the current revision—by selecting the row with the highest revision for a particular product_id—will be useful.

这篇关于以有效的方式存储关系对象的修订的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！