数据库设计头脑风暴：销售价格 [英] Database Design Brainstorming: Sale Prices

问题描述

我需要创建一个数据库解决方案，为客户提供产品折扣。

I need to create a database solution to provide product discounting.

当前表：

Products
Columns: ProductId, ProductTypeId, ReleaseDate

ProductPrices
Columns: ProductPriceId, ProductPriceTypeId (one product may have n prices), ProductId, Price

我们希望能够通过产品编号和/或ProductTypeId和/或ProductPriceTypeId和/或RELEASEDATE打折。

We want to be able to discount by ProductId and/or ProductTypeId and/or ProductPriceTypeId and/or ReleaseDate.

例如销售：

1. 折扣单一产品编号


2. 折扣指定ProductTypeId的所有产品和
   ProductPriceTypeId。


3. 折扣与最近一个月内的
   RELEASEDATE指定ProductTypeId的所有产品。

＃2具有挑战性的方面是没有文字的例子，但考虑到新的领域在未来添加一个事件的长期可扩展性

The challenging aspect of #2 is not the literal example, but considering long term scalability in the event new fields are added in the future.

我难倒了如何处理因为RELEASEDATE＃3。

I am stumped how to handle #3 because of the ReleaseDate.

下面，是我精神以为之前，我意识到我需要来＃1。你可以看到，刚性结构不会允许因为明确包含列的可扩展性好 - 如果我们增加了未来的新标准，那么这些列将需要添加到表 - 更不用说它甚至不处理。RELEASEDATE要求

Below, is what I mentally thought out before I realized I needed to come to Stackoverflow. You can see that the rigid structure will not allow for good scalability because of the explicitly included columns - if we added new criteria in the future then those columns would need to be added to the table - and not to mention it does not even handle the ReleaseDate requirement.

新表：

ProductPriceDiscounts
Columns: ProductPriceDiscountId, ProductPriceId, ProductTypeId, ProductPriceTypeId, Discount, DiscountTypeId (1 for percentage, 2 for fixed)

然后我可以用这样的事情，以获得定价：在ProductPrices

Then I could use something like this to get the pricing:

from p in Products
join pp in ProductPrices on p.ProductId equals pp.ProductId
let ppd = (
    from ppd in ProductPriceDiscounts
        .WhereIf(ppd.ProductPriceId != null, ppd.ProductPriceId == pp.ProductPriceId)
        .WhereIf(ppd.ProductTypeId != null, ppd.ProductTypeId == pp.ProductTypeId )
        .WhereIf(ppd.ProductPriceTypeId != null, ppd.ProductPriceTypeId == pp.ProductPriceId)
    select ppd).FirstOrDefault()
where p.ProductId = productId
select new 
{
    ...,
    Price = pp.Price,
    Discount = pp.Discount,
    DiscountedPrice = 
        (ppd.DiscountTypeId == 1) ? 
            (pp.Price - (pp.Price * pp.Discount)) :
            (pp.Price - pp.Discount) :
}

我只包含这个坏榜样，我想出了以显示我需要怎么能够使用这种新产品的折扣功能的最终结果。任何人都可以处理这种情况的一个很好的方式提供建议？谢谢你。

I only included this bad example I came up with to show the end result of how I need to be able to use this new product discounting functionality. Can anyone offer advice for a good way to handle this situation? Thanks.

推荐答案

我结束了我最初的设计去，当我查询数据库使用LINQ到SQL，我得到的DiscountedPrice使用的方法。该方法运行其自己的查询和返回结果之前，它执行条件逻辑。因此，这让我有我的代码的选项做额外的工作，我的查询将不能够。我还做了一个枚举类特殊ProductPriceDiscountIds，使他们能够很容易识别。我希望这可以帮助别人谁发现自己处于类似的情况 - 它的工作对我非常好，到目前为止

I ended up going with my original design, and when I query the database using linq to sql, I get the "DiscountedPrice" using a method. The method runs its own query and performs conditional logic on it before returning the results. So this gives me the option of having my code do additional work that my query wouldn't be able to. I also made an Enum class for the special ProductPriceDiscountIds so they can be easily identified. I hope this helps anyone else who finds themselves in a similar situation - it's working very well for me so far.

这篇关于数据库设计头脑风暴：销售价格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！