这是一个很好的设计，具有大量记录的审计表？ [英] Is this a good design for an audit table with tons of records?
问题描述
我有一个表格可以跟踪每个单独的库存数据。这是表的简化版本（一些非关键字段被排除在外）：
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
每一次给定的东西发生一些事情，都会创建一个新的审计记录。例如，我的产品ABC第一次添加到库存中，我会得到如下记录：
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
如果ABC序列的成本数字555变化，我得到一个新的记录：
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
如果一个新的ABC被带入，我得到这个记录：
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.
使用上面的示例，如果我想获得ABC产品的库存值，截至01/02/2009，我需要为每个唯一的产品/ SerialNumber组合，最近的记录之前到01/03/2009，状态为OnHand，然后将成本加起来。 （我不是100％肯定这个选择语句在这一点上会是什么样子，但我会尝试一下）。
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.)
我应该将历史记录分解成单独的表，只剩下最多活动表中每个ProductID / SerialNumber组合的最近记录？
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.
谢谢！
推荐答案
您将使您的生活更容易，拥有与审核数据分离的实时数据表，这是一个非常好的理念。对于正常的日常操作，您不需要甚至查看审核数据，使其具有与实时数据相同的表格，只会导致头痛。
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.
这篇关于这是一个很好的设计，具有大量记录的审计表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
