Post reply

Many to Many RelationShip with type 2 SCD's on both sides

  • July 3, 2012 at 3:50 am

    #258288

    Perhaps a classic example that i have missed,

    but i am looking for a solution to design this. This is a simplified example for illustration. Hope it is clear. Currently we log type 2 changes for our products (with IsCurrent and EffectiveDates in the dimenson also for reference).

    I would like to add more information related to the suppliers of our products, and also log changes.

    SO we have multiple products from multiple suppliers (Many to Many relationship) with SCD type 2's related to the item itself, and SCD type 2's related to the combination of the Product and the Supplier.

    Example:

    DimProduct contains on SCD2: LifeCycleStatus (our LifeCycleStatus), for example: BrandNew, EndOfLine etc. This is our assesment of the product status.

    DimSupplierProduct contains an SCD2: SupplierLifeCycleStatus,for example: BrandNew, EndOfLine etc. For the same item different suppliers can have a different assesment of the product.

    Standard option based on what i have read would be a bridge table for the Many 2 Many relationship between products and suppliers. But how to build the link? You have to incorperate a scheme to adjust for the changing of the surrogate key on both sides when a SCD typ2 change occurs on either side (occur independently from each other). Is this possible?

    Or link DimSupplierProduct directly to the facttables based on the business key/natural key (ProductCode in my example)?

    Or add fields to DimProducts for these supplier related fields: Supplier1XXX, Supplier2XXX and limit those to the 2 most important suppliers to avoid to many fields to a dimension?

    Any thoughts on this would be welcome!

  • July 16, 2012 at 6:06 am

    #1513021

    GRUNNINGER (7/3/2012)

    ... we have multiple products from multiple suppliers (Many to Many relationship)...

    Break the many-to-many relationship - add a bridge table.

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply