产品“特价”的数据库表示特价商品因领域而异
我正在尝试找出在我的数据库中表示“特价”概念的最佳方式 - 这些与“待售产品”相关。每个特价都会提供某种折扣，但它们会有所不同，其中一个只是简单的降价，但其他折扣会是“买 x 送 y 半价”或“类别 x 30% 折扣”之类的东西。我预计不会有超过 10 种左右的特价类型……但需求会发生变化，我需要确保在需要时我可以处理更多类型。
我试图想出一种简单的方法来在我的数据库中表示这些，但是在我看来，我要么必须为每个特殊创建一个表，要么必须有一个包含大量列的特殊表其中 90% 的值将为 NULL。我打算有一个 SpecialType 表，它将按类型将所有特价商品分组在一起，例如“折扣”、“买 x 送 y”，这将用于确定相应应用程序层中的业务逻辑。此外，我在想我可以沿着每个特殊类型有 1 个表的路径，然后缓存可以定期更新的结果（本质上是缓存每个产品的可渲染输出），例如每 5 分钟更新一次，这样做的目的是是执行大量连接（编辑 - 这些实际上可能是 UNIONS），但执行频率相当低，因为数据不是实时关键的，因为 5 分钟的延迟不会杀死任何人。
对于如何以最简洁的方式处理这种情况同时保持数据库规范化的任何反馈，我将不胜感激。
I am trying to work out the best way to represent the concept of "Specials" in my database - these relate to "products for sale". Each special will offer some sort of discount however they will vary in the sense that one will just be a simple price reduction but others will be things like "Buy x Get y Half Price" or "30% off category x". I don't anticipate having more than around 10 specials types... but requirements change and I need to make sure I can handle more if the need arises.
I am trying to come up with a simple way to represent these in my DB however the way I see it I am either going to have to create a table per special or I am going to have to have a specials table with loads of columns of which 90% of the values will be NULL. I intend to have a SpecialType table which will group all specials together by type e.g. "Discount","Buy x get y Free" and this will be used to determine the business logic in the respective app layer. Additionally I was thinking I could go down the path of having 1 table per special type and then caching the results (essentially caching the renderable output per product) that could be updated on a regular basis, say every 5 minutes, the intention of this would be to perform a large number of joins (EDIT - these would actually probably be UNIONS) but do it fairly infrequently as the data is not real-time critical in the sense that a 5 minute delay wouldn't kill anyone.
I would appreciate any feedback on how to handle this situation in the tidiest manner whilst keeping my database normalized.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于添加特殊类型将需要更改代码，并且您无法合理地预期所有可能的特殊类型，因此我很乐意为每种特殊类型创建一个单独的表，以及某种主表，其中将包含特殊类型、名称、描述等...
使用包含大量不适用字段的单个表往往会成为
顺便说一句，我一生中最不愉快的惊喜之一是当我发现一项新的促销活动需要从电视广告中更改代码/数据库时。
Since an addition of a Special type will require a code change, and you cannot reasonably anticipate all possible special types, I would be comfortable to create a separate table for each type of special, along with some kind of master table, that would contain the special types, names, descriptions, etc...
Using a single table with lots of inapplicable fields will tend to become an Inner Platform and that is almost always an overkill.
BTW, one of the most unpleasant surprises in my life, was when I found out about a new promotion that required code/database changes from a TV commercial.