如何正确地将项目与价格数据相关联，并考虑到价格变动历史

问题描述

实际上所有POS系统在销售时直接将交易项目的价格直接记录到交易表中，因为该价格可能会在以后更改，但其出售价格应保持不变。

我想知道如何设置保留价格变动历史的定价表，以便您可以根据项目和时间将交易与该表相关联出售以获得正确的价格？

我认为任何曾经与POS系统合作的人都会明白我在说什么，但如果我的问题不清楚我知道，我会尝试更好地解释。

我没有标记特定的数据库，因为这个问题显然不是特定的，而是因为我知道这是第一个问题：SQL Server 2008。

编辑：

这里有一个解决方案只是想到我会喜欢得到一些反馈...

我想我可以有一个价格表，有这样的领域：
PriceId，ItemId，Price，Date

PriceId将是一个自动编号，ItemId与Items表相关。现在，而不是将实际价格保存到交易表中，我将保存给定项目的最新PriceId。

想法？

解决方案

我真的不明白这样做的原因。您正在为添加额外的加入，几乎所有分析目的和大多数查询，这将影响应用程序的速度。

回答您的问题尽管最简单的方法是创建定价表，就像您正在创建一样。

所以，做一些假设，你的定价表可能有以下列：

  id int，pk 
 product_id，fk into products 
 price_start_date date 
 price_end_date date 
 price number（x，2）
  

假设您的订单表具有订单放置/发布等的日期，无论您用于确定每个的价格>查询确定价格将成为

 选择价格
从定价p 
加入订单o 
 on o.order_Date p.price_start_date和p.price_end_date 
  

然而，正如我所说，我不会做这个。我可以想到的唯一的障碍就是在订单表中单独列出历史价格，就是使历史价格分析在DB上稍微重一些。你不会经常这样做，而不想知道有多少单位出售，因此需要使用订单表，所以我不认为这有很大的区别。

我认为这是其中一个非常稍微非正规化数据库的事件绝对是一个积极的而不是一个负面的。

---

好的，关于你的编辑，我建议的差别很小。我将假定您的表格在 itemid 和 date （don不要将此作为实际的列名称），否则可以在items表中保持同样的日期。

然而，它这意味着每当您向事务表中添加内容时，您必须使用聚合查询来计算每个项目的最新价格。我的建议是使更新价格更加困难，但是更便于实现价格。当你计算一个物品的价格比你更新一个价格的时候，我个人认为我会建议的表现。

Virtually all POS systems record the price of an item directly to the transactions table at the time of the sale since that price may change at a later date, but the price it sold at should remain the same.

I'm wondering how you would setup pricing table(s) that keep a history of price changes, so that you can relate the transactions to that table based on the item and the time it was sold in order to get the correct price?

I think anyone who's ever worked with POS systems will understand what I'm talking about here, but if my question is unclear let me know and I will try to explain better.

I didn't tag a specific database because this question obviously isn't specific to a particular one, but because I know it's the first question I'll be asked: SQL Server 2008.

EDIT:

Here's one solution I just thought of that I would love to get some feedback on...

I'm thinking I could have a price table that has fields like so:PriceId, ItemId, Price, Date

PriceId would be an autonumber and ItemId relates to the Items table. Now instead of saving the actual price to the transactions table, I would save the most current PriceId for the given item.

Thoughts?

解决方案

I don't really see a reason for doing this. You're adding an extra join for almost all analytic purposes and most queries, which will impact the speed of the application.

Answering your question though the easiest thing would be to create your pricing tables as if you were creating a temporal database.

So, making a few assumptions, your pricing table might have the columns:

id int, pk
product_id, fk into products
price_start_date date
price_end_date date
price number(x, 2)

Assuming your "orders" table has the date that the order was placed on / posted etc, whatever you use to determine price every query to determine price would become

select price
  from pricing p
  join orders o
    on o.order_Date between p.price_start_date and p.price_end_date

However, as I say I would not do this. The only hindrance, that I can think of, to having historical prices in the orders table as opposed to a separate one is that it makes historical prices analyses slightly heavier on the DB. You're not going to do this very often without also wanting to know how many units are sold and thus needing to use the orders table anyway so I don't think it makes a big difference.

I think this is one of those occurrences where a very slightly denormalised database is definitely a positive rather than a negative.

---

Okay, regarding your edit there is very little difference to what I have suggested. I'm going to assume that your table is unique on itemid and date (don't use this as the actual column name) otherwise the date could be held equally well in your "items" table.

However, it does mean that whenever you add something to the transactions table you have to use an aggregate query to work out the most recent price for each item. My suggestion makes updating a price more difficult but working out the price easier. As you calculate the price of an item more regularly than you update a price I'd, personally, take the performance hit where I've suggested.

这篇关于如何正确地将项目与价格数据相关联，并考虑到价格变动历史的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！