Microsoft Office Tutorials and References
In Depth Information
From the data model point of view, each line of an order might refer to more than one reason.
Therefore, you cannot add a SalesReasonID column to the SalesOrderDetail table because, when
you do that, you can define only a single reason for each line, whereas your data model requires
a variable number of reasons for each single line.
You can clearly see the situation in Figure 4-32. If you set up a relationship between
FactInternetSales and DimSalesReason, you can slice sales by sales reason, but you are
limited to only one sales reason for each order line.
FIguRE 4-32 The wrong way to model sales reasons.
To set up the relationship correctly, you need an additional table. If you add an intermediate
table that contains an order line and a sales reason, you can use this additional table to define
both many sales reasons for a single line of an order and many lines of orders for a single sales
reason. This is why these kinds of relationships are called many-to-many: if you need to relate
many rows in a table with many rows in another one, you can do that using an intermediate
table. This intermediate table acts as a bridge between the two original tables: therefore, it is
commonly called a bridge table .