none
one to many in data model RRS feed

  • Question

  • Hi,

    What is the best way to design a composite key or bridge table in Product table where i have repeating column values.I am trying to establish proper relationship between fact and Product dimension.The structure of DimProduct view is as below and no column can be a primary key by itself,so all 4 columns together form a key.

    • Product
    • ProductLine
    • ProductFamily
    • ProductCategory

    Fact table has columns like: 

    • Margin
    • Revenue
    • Product
    • ProductLine
    • ProductFamily
    • ProductCategory

    There is a many to many relationship here.How do i best design the key for the Product view.And please note that the Product is a view,not a table.So if i concatenate the 4 columns to make a key,i would still need a surrogate key(numeric) as best design principle.How do i go about it and store that as foreign key in Fact table.

    I want to be then able to filter fact using any of the Product fields.

    Friday, January 10, 2020 5:46 PM

All replies

  • I'm not sure that I understand this. If (Product, ProductLine, ProductFamily, ProductCategory) is a unique key, how can there be a many-to-many relation to the fact table?

    I know that in datewarehousing designs there is some obsession with one-column keys, but since I'm more of an OLTP person, I don't see any problem with a four-column key.

    If you want unique single-column key in the Product table that would permit you to rip out the four columns in the Fact table, you will need to go back to the definition of the view, because the id must be constructed in such a way that it does not change if a row is added or deleted from the underlying table.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 11, 2020 10:25 AM
  • Hi,

    Can you check the option mentioned here using Boolean and bridge table for setting up many to many relationship:

    https://gerardnico.com/data/modeling/many-to-many

    Regards,

    Srihad

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it . Blog - srihashadari.com

    Saturday, January 11, 2020 4:37 PM
  • Erland,

    Currently no column values are unique.I am thinking of the consequence of having a composite key and when users want to filter the fact table with each of the  4 columns.How would the fact behave ?

    Monday, January 13, 2020 3:01 PM
  • These items all stand on their own. You should have 4 dims:  Product, ProductLine, ProductFamily, ProductCategory.  
    Monday, January 13, 2020 5:31 PM
    Answerer
  • Tom,

    Can you please explain the consequence.

    Monday, January 13, 2020 5:34 PM
  • Your database should look like this:

    Monday, January 13, 2020 5:57 PM
    Answerer
  • I did understand when you had said they should all be separate dimensions.But i am not clear on what will be the problem if i have them all as composite key and try to filter by each of them in the report.
    Monday, January 13, 2020 6:10 PM
  • You had a requirement of searching for any of the products.

    When you search for ProductCategory = "X".  You will need to get every product/productline/productfamily combination where productcategory="X".  This will be very inefficient.

    You will be much happier if you just separate them now.

    Monday, January 13, 2020 6:22 PM
    Answerer
  • Currently no column values are unique.I am thinking of the consequence of having a composite key and when users want to filter the fact table with each of the  4 columns.How would the fact behave ?

    Depends on wheter you have index on the compositie key or not. If there is on index, the filtering wil require a scan. Not fun.

    Maybe you should to as Tom suggested, since this is a data warehouse guy. But from a purisitic relational standpoint, it should be a composit key.

    ...maybe. After all, I know nothing about the business rules, the data or anything, so how could I know what I'm talking about.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Monday, January 13, 2020 11:03 PM