Can a Bridge Table also be used for mapping?

I am working with a dimensional model that is related to reduction of costs during the purchasing process. When items are purchased they are often not purchased from a preferred vendor.

The idea I was presented with was to have a dimension that would contain the vendor item (not preferred) and the preferred item in the same row. The idea is to map the vendor item in a PO to the preferred item. This would lead to adding rows to the dimension for each mapped transaction. The other concept handled in this dimension would be the ability to track pricing changes for the preferred vendor. So the idea is to have this dimension work as a SCD. I am clear on how to implement a SCD. My plan is to keep the preferred item dimension clean and implement it as a SCD.

The main issue for me is how or where to place the mapping of items that are not preferred in the model. I wonder if the mapping should be done outside of the model altogether? Since the transaction in this case might be from a non preferred vendor my thought was to create a structure to capture this information in a regular SQL table. So the mapping would occur separately. Then this table or relational db structure could be used to populate the fact table together with the preferred vendor dimension. So the fact table after ETL processing would contain the item pricing on the PO and also the preferred vendor pricing to deduce the difference costs based on the PO event.

My latest idea after reading more about bridge tables and remembering my ERD days creating an associative or junction table, is to have a bridge table that would use the dimension key and the fact table key as a method to map transaction items to the preferred items. I have seen in more than one article or post that states that using the key from the fact table and the dimension in a bridge table is a straight forward method to map or handle for many to many using a bridge table.

Here's what I have so far for the fact table and the dimension. I have included the original idea for the dimension below and added in a mapping table as well.

FactPurchaseOrder (Fact Table)

PrefererredItemKey
PurchaseOrderDate
PurchaseOrderNumber
PurchaseOrderLineItem
TransactionQuantity
TransactionITemCost
PreferredItemCost

PreferredVendor (Dimension) - Proposed orginal idea as SCD that includes both preferred and a non preferred vendor.

Vendor
VendorItemNumber
PreferrredVendor
PreferredVendorItemNumber
PreferredItemCost
PreferredUnitCalc

PreferredVendor (Dimension) - SCD that does not include both vendors.

PreferrredVendor
PreferredVendorItemNumber
PreferredItemCost
PreferredUnitCalc

Bridge Table (Use for mapping and can handle for many to many, sits in between the fact table and dimension with only preferred vendor items.)

TransactionKey (surrogate key from fact table)
PreferredVendorKey (surrogate key from dimension)
Weighting
descriptive field1
descriptive field2
MappingDate
MappingType

I am wondering if the first idea proposed is manageable method to handle the vendor mapping in the preferred vendor dimension? After consideration of this idea it would appear that it would lead to a one to one rather than a one to many between the dimension and the fact table which is pretty much standard. It seems like the concept of mapping can be modeled by what dimensional modelers call a bridge table.

In short, after reading about bridge tables which are used for to handle for (many to many) scenarios, I am favoring using a bridge table that will sit in between the dimension and the fact table. This way if it turns out that there are potentially more than one preferred vendor for a transaction I already have a method in place to handle for it.

Thank you!

If there are only two vendors, then I would just create two relationships from the vendor dimension. If there can be 1..n number of vendors, then a bridge table makes sense.

It doesn't make sense to combine preferred vendor or item with the actual vendor or item in a dimension. Vendors are vendors and items are items. Which ones are preferred is a matter of building the relationship between vendors and items, and items and items over time.

The relationship is maintained using fact tables (one for preferred vendor of items, the other preferred items over other items) that reflect the particular state for a period of time. You can then use these tables in queries to evaluate the use of preferred vendors and items in actual purchase transactions.

Hello ngalemmo,

When you say below "the other preferred items over the other items", do you mean the non preferred items? I have setup two fact tables one with preferred facts and the other for non preferred facts. Each of these fact tables are related to the same dimensions. Is this what you meant below?

ngalemmo wrote:It doesn't make sense to combine preferred vendor or item with the actual vendor or item in a dimension. Vendors are vendors and items are items. Which ones are preferred is a matter of building the relationship between vendors and items, and items and items over time.

The relationship is maintained using fact tables (one for preferred vendor of items, the other preferred items over other items) that reflect the particular state for a period of time. You can then use these tables in queries to evaluate the use of preferred vendors and items in actual purchase transactions.