表上的派生状态字段？
我的客户是一家长期租赁机构。 因此，我们
Goods
pk modelId
Stock
pk stockId, fk modelId
Rentals ( rented stock)
pk rentalId fk stockId
还有库存可能会呈现其他状态，例如正在维修等，并且可能同时具有多种状态。
将状态字段放在股票表上将使我的生活更轻松，但最终它是我可以从其他表中推断出的派生数据。 一个例子是查找库存商品（未出租）的总数，包括库存数量为 0 的商品。 我是否应该
- 将租用、onRepair 等状态字段添加到库存表中，
- 维护现有库存字段
每次都查找它？
ps 库存和货物表是分开的，因为有一个比我在这里解释的库存类型（即制造商等）更复杂的分类法
My client is a long term rental agency.
So we have
Goods
pk modelId
Stock
pk stockId, fk modelId
Rentals ( rented stock)
pk rentalId fk stockId
In addition stock may take on other statuses such as being repaired etc and may have more than one status at once.
It will make my life easier to put a status field(s) on the stock table but ultimately it's derived data that I can infer from other tables.
An example is finding the total number of goods in stock ( not rented ) including the ones with 0 qty in stock.
Should I
- add rented, onRepair etc status fields to the stock table
- maintain a stock on hand field
look it up every time?
ps the stock and goods tables are separate as there is a more complex taxonomy than I have explained here for the types of the stock i.e. manufacturers etc
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果状态或其他属性可以从其他表中派生，则更容易实现的选项是保持原样并“每次都查找”。您的表格保持标准化。并且您不必使用触发器等复杂的解决方案来维护任何派生值。
您什么时候必须考虑其他选项？当上述方法变得缓慢时 - 即使您已经优化了查询。例如，如果您必须计算 10 年来的股票价格以及数千种股票和商品的平均值，则查询速度将不够快，无法即时计算。
因此，您可以首先添加包含所需聚合数据或其他派生数据的表或字段。您可以使用索引视图（大多数 RDBMS 具有该功能）或附加表来存储聚合数据 - 这对于无法再更改的数据非常有效，例如前几天/前几个月/前几年的股票价格统计数据。当一天/一个月/一年结束时，计算完成（一次）并更新表格。然后，您可以快速搜索这些表中的旧数据和“活动”表中的新数据。
针对更复杂情况的其他选项包括维护派生值的触发器。我会将此作为最后的资源，但我确信对此事存在相反的看法。
If the status or other attribute can be derived from other tables, the easier option to implement is to keep it as it is and "look it up every time". Your tables stay normalized. and you don't have to maintain any derived values with complex solutions like triggers, etc.
When do you have to think about the other option? When the above approach is becoming slow - even though you have optimized your queries. If you have to calculate the averages for example of stock prices over 10 years and for thousands of stocks and goods, the queries will not be fast enough to be calculated on the fly.
So, you can first add tables or fields with aggregate or other derived data that you need. You can used indexed views (most RDBMS have that feature) or additional tables for aggregate data - this can work nicely for data that can't change any more, example statistical data for stock prices from previous days/months/years. When a day/month/year ends, the calculations are done (once) and the tables are updated. Then, you can seacrh fast those tables for older data and the "active" tables for newer data.
Other options for more complex situations include triggers that maintain the derived values. I would use this as a last resource but I'm sure there are opposite views on this matter.