对于包含大量记录的审计表来说，这是一个好的设计吗？
我有一个表，用于跟踪每件商品的库存数据。 这是该表的简化版本（排除了一些非关键字段）：
UniqueID,
ProductSKU,
SerialNumber,
OnHandStatus,
Cost,
DateTimeStamp
每当给定部分发生某些情况时，就会创建一条新的审计记录。 例如，当我的产品 ABC 第一次添加到库存中时，我会得到如下记录：
1, ABC, 555, OnHand, $500, 01/01/2009 @ 02:05:22
如果 ABC 序列号 555 的成本发生变化，我会得到一条新记录：
2, ABC, 555, OnHand, $600, 01/02/2009 @ 04:25:11
如果该产品已售出，我会得到另一条记录：
3, ABC, 555, Sold, $600, 02/01/2009 @ 5:55:55
如果引入新的 ABC 后，我得到了这样的记录：
4, ABC, 888, OnHand, $600, 02/05/2009 @ 9:01:01
我需要能够在任何时间点尽快获取一组给定产品的现有库存价值。
使用上面的示例，如果我想获取截至 2009 年 1 月 2 日产品 ABC 的库存价值，我需要为每个唯一的产品/序列号组合选择单个最新记录早于 2009 年 1 月 3 日，状态为“OnHand”，然后将成本相加。 （我现在还不能 100% 确定这个 select 语句会是什么样子，但我将进行一些实验）。
我的问题：对于我所描述的审计表类型来说，这是一个好的结构吗？ 也就是说，如果索引适当，它是否适合快速查询？ （我试图想象当该表增长到数百万行时会发生什么。）
我是否应该将历史记录分解到一个单独的表中，并只在“活动”表中保留每个 ProductID/SerialNumber 组合的最新记录？
如有任何反馈/建议/评论/链接，我们将不胜感激。
谢谢！
I have a table that tracks inventory data by each individual piece. This is a simplified version of the table (some non-key fields are excluded):
UniqueID,
ProductSKU,
SerialNumber,
OnHandStatus,
Cost,
DateTimeStamp
Every time something happens to a given piece, a new audit record is created. For example, the first time my product ABC gets added to inventory I get a record like this:
1, ABC, 555, OnHand, $500, 01/01/2009 @ 02:05:22
If the cost of ABC serial number 555 changes, I get a new record:
2, ABC, 555, OnHand, $600, 01/02/2009 @ 04:25:11
If the piece is sold, I get yet another record:
3, ABC, 555, Sold, $600, 02/01/2009 @ 5:55:55
If a new piece of ABC is brought in, I get this record:
4, ABC, 888, OnHand, $600, 02/05/2009 @ 9:01:01
I need to be able to get on-hand inventory value for a given set of products at any point in time as fast as possible.
Using my example above, if I wanted to get my inventory value for product ABC as of 01/02/2009, I'd need to select, for each unique Product/SerialNumber combination, the single most recent record prior to 01/03/2009 with a status of "OnHand" and then add up the costs. (I'm not 100% sure what this select statement would look like at this point, but I'm going to experiment a bit).
My questions: is this a good structure for the type of audit table I'm describing? That is, does it lend itself to fast queries if indexed appropriately? (I'm trying to imagine what will happen when this table grows to millions of rows.)
Should I break out historical records into a separate table and only leave the most recent record for each ProductID/SerialNumber combo in the "active" table?
Any feedback/suggestions/comments/links are appreciated.
Thanks!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
将实时数据表与审计数据分开，会让您的生活变得更加轻松，这是一个非常好的主意。 对于正常的日常操作，您甚至不需要查看审核数据，因此将其与实时数据放在同一张表中只会引起麻烦。
管理此问题的最简单方法是在活动表上放置一个触发器，以便每当插入/删除/更新记录时，它都会自动将新记录插入审计表中。
编辑：扩展Kevin 对此的想法，我想，无论序列号如何，所有共享相同 SKU 的产品都会有相同的价格？ 如果是这种情况，拥有单独的价格表绝对是个好主意。
You'll make your life a lot easier having a table of live data separate from your audit data, it's a very good idea. For normal day-to-day operation you shouldn't need to even look at the audit data so having it the same table as your live data is just going to cause headaches.
The easiest way to manage this would be to put a trigger on the live table so that whenever a record is inserted/deleted/updated it automagically inserts a new record into the audit table.
edit: expanding on Kevin's thoughts on this, I would imagine that regardless of the serial number, all pieces sharing the same SKU would have the same price? If this is the case, having a separate price table is definitely a good idea too.
并非所有值都会立即更新，为什么要重现所有静态信息？ 我认为你应该有不同的序列号、状态和成本表。 每个表还将包含产品 ID 和更新日期。
这样，您还可以轻松判断产品的哪一部分发生了变化。 以前，您需要将产品的所有字段与第一个产品之前保存的产品的所有字段进行比较。
Not all values are updated at once, why reproduce all the information that is static? I think you should have different tables for serial number, status, and cost. Each of those tables will also have the product ID and the updated date.
This way, you could also easily tell which part of the product has changed. Before, you would have the compare all the fields of the product with all the fields of the product that was saved just before the first one.
您将需要分离您的审计数据。 随着时间的推移，将当前数据与审计数据放在一起会影响性能。
最简单的实现是创建一个与生产具有相同架构的单独数据库。 向审核数据库中的每个表添加日期时间戳。 根据生产主键和新的日期时间戳创建复合主键。
在生产数据库上设置一个触发器，以便生产数据库中的每个插入/更新都会触发对审核数据库的插入。 插入审计数据库的值将是新插入的值。
仅将审核数据库用于审核报告目的。
或者，您还可以考虑创建一个数据集市，该数据集市将负责跟踪随时间的变化。 （但这需要花费大量的时间和精力）
You will need to separate your audit data. Keeping current data together with audit data will impact performance as time progresses.
The simplest implementation would be to create a separate database with the same schema as production. Add a datetime stamp to each table in the audit database. Make a composite primary key from the production primary key and the new datetime stamp.
Setup a trigger on the production database so each insert/update in the production database fires off an insert into the audit database. The values inserted into the audit database will be the newly inserted values.
Only use the audit database for audit reporting purposes.
Alternatively, You can also look at creating a data-mart which would be responsible for tracking changes over time. (But that takes a lot of time and effort)
首先，一些定义（不是临床定义，只是我自己的思想分离命名法）：
==========
初始表：您添加到其中并从中检索的日常表。
审核表：该表保存其相关初始表中任何记录的多个版本。
==========
如果审计表的业务用途是能够知道记录在任何时间点的样子，我认为它的构造应该与初始表相同（加上添加唯一的审核 ID）。
如果更重要的是了解任意时间点的字段值（而不是整个记录），那么请尝试更缩写的表-字段-值-日期方法。 请注意，使用这种方法重建整个记录需要做更多的工作，所以如果可能需要整个记录检索，请忘记它。
总的来说，我认为在大多数情况下，使用最新版本的记录的快速性能比使用审计数据的性能更重要。 因此，我建议创建与初始表相同的审计表（加上自动编号代理键），并在添加到初始表时触发将相同数据插入到审计表中。 这使初始表中的记录数量保持相对静态，并且性能不会随着时间的推移而降低。
First, a bit of definition (not clinical defs, just my own separation-of-ideas nomenclature):
==========
Initial table: The day-to-day table that you add to and retrieve from.
Audit table: The table holding multiple versions of any record in its related Initial table.
==========
If the business use of an audit table is to be able to tell what a record looked like at any point in time, I'd say it should be constructed identically to the initial table (plus the addition of a unique audit-ID).
If it is more important to know what a field value was at any point in time (as opposed to the record as a whole) is, then try the more abbreviated table-field-value-date approach. Please note that it takes a lot more work to reconstruct the entire record with this approach, so forget it if whole-record retrieval might ever become necessary.
Overall, I think that in most cases, fast performance using the most recent version of a record are more important than performance using audit data. Consequently, I'd suggest creating the audit table identically to the initial table (plus autonumbered surrogate key), and trigger an insert of the same data into the audit table when added to the initial table. This keeps the number of records relatively static in the initial table, and performance doesn't degrade over time.