问题描述
我试图从似乎包含桥表的E / R图（OLTP系统）构建星型模式。订单是显而易见的事实表，产品是维度表。如果模型需要为星型架构，我看不到如何保存桥接表。如果我需要在模型中保留有关渠道的信息，您将如何处理这种关系？
I am trying to build a star schema from an E/R diagram (OLTP system) that seems to contain a bridge table. Order is an obvious fact-table and product a dimension-table. I can't see how I can keep the bridge table if the model needs to be a star schema. How would you tackle this relationship if I need to keep information about Channel in the model?
推荐答案
这取决于您计划使用模型的方式。
It depends on how you plan to use the model.
如果您只需要回答有关现有订单的产品和渠道问题，则可以完全避免桥接表，因为尽管可以通过以下方式解决渠道和产品之间的M2M关系事实表（订单）：
If you only need to answer product and channel questions about existing orders, then you can avoid the bridge table altogether, because M2M relations between channels and products can be resolved though the fact table ("Orders"):
这种设计的（巨大）优势是其简单性和易用性-对最终用户而言非常直观。它也很快。
The (huge) advantage of this design is its simplicity and ease of use - it's very intuitive to the end-users. It's also fast.
该模型的缺点是它对订单的依赖性。如果没有订单（即事实表中没有订单），则您将无法回答有关产品和渠道关系的问题（例如，按分配的渠道向我展示所有产品）。如果这些问题不重要，而您只需要分析现有订单，则保持简单即可。
The disadvantage of the model is its dependency on the orders. If orders are absent (i.e, no orders in the fact table), then you won't be able to answer questions about product and channel relations (for example, "show me all products by their assigned channels"). If such questions are not important and you only need to analyze existing orders, keep it simple.
如果即使在没有现有订单的情况下也确实需要分析产品渠道关系，那么事情就更加复杂了。一种方法是如下添加桥表：
If you do need to analyze product-channel relations even without existing orders, then things are more complicated. One approach is to add a bridge table as follows:
此设计的优势在于，无论订单如何，渠道-产品关系始终可用。按产品分析订单也很简单。缺点是现在更难以按渠道分析订单，因为您现在必须仔细检查过渡表。例如，在最终用户工具（如Power BI）中，您将需要建立红色双向连接，以使过滤器从通道尺寸通过桥接传播到产品尺寸。当然，这是可行的，但最终用户现在必须知道他们在做什么，这不再是简单的事情。
The advantage of this design is that Channel-Product relations are always available, regardless of the orders. It's also (still) simple to analyze orders by product. The disadvantage is that it's now harder to analyze orders by channel, because you now have to go through the bridge table. For example, in end-user tools such as Power BI you will need to make the "red" connection bi-directional, to enable filter propagation from the channel dimension via bridge to the product dimension. It's doable, of course, but end-users now will have to know what they are doing - it's no longer simple.
另一种设计使用事实事实表：
Yet another design uses "factless" fact table:
在这里，您可以轻松查询无订单的渠道-产品关系（通过事实表Product-Channel，该表实际上显示了关系状态），还可以轻松查询产品和渠道订单。您也可以钻取这样的结构来回答关于产品的各种复杂问题，而无需现有订单。但是，这种设计并不像第一个那样直观。
Here, you can easily query Channel-Product relations without orders (through the factless fact table Product-Channel, which shows essentially relationships status), and also easily query orders by both product and channel. You can also "drill-across" such structure to answer all kinds of complex questions about products without existing orders. Still, such design is not as intuitive as the first one.
这篇关于如何在Star Schema中处理Bridge表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！