Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    4

    Resolving Many to Many

    Hi

    I have a an entity A, entity B and Entity C.A has a Many to Many relationship with B and C. I created associate tables rel_a_b and rel_a_c.
    A,B and C are all Slow changing dimensions. I have surrogate Keys for each of these tables that gets changed whenever a description (or any scd column chaages). I have the SKs as the composite key in rel_a_b and rel_a_c. rel_a_b and rel_a_c has some attributes that can change like accociation_type, prorate, etc. I can include a SK here to take care of this and move the SKs out of PK or I can include the current_date in the PK to track that. The question I have here is if there is small change in (say desc) in entitiy A, then I have repopulate rel_a_b and rel_a_c with the new SKs so that the description is latest when queried from dimension C or B perspective. Now rel_a_b and rel_a_c could be lot of rows. Is there a way I can avoid populating these associate tbales with SKs whenever there is small chnage and populate that table only when the relationship chnages.

    Any help is greatly appreciated.

    Thanks

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why does your SK need to change whenever an SCD column changes?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    SCD is Kimball dimensional terminology but this doesn't sound much like a regular star or snowflake schema.

    In the association tables why not use the business key from the A,B,C entities?

  4. #4
    Join Date
    May 2008
    Posts
    4
    Quote Originally Posted by r937
    why does your SK need to change whenever an SCD column changes?
    Thanks for the response. If I don't change the SK then I have to include the start date in the key. currently I have just the SK as the PK in the dimension tables, so that when a change occurs I change the SK and insert another row (type 2) with the new values.

    Thanks

  5. #5
    Join Date
    May 2008
    Posts
    4
    Quote Originally Posted by dportas
    SCD is Kimball dimensional terminology but this doesn't sound much like a regular star or snowflake schema.

    In the association tables why not use the business key from the A,B,C entities?
    Thanks for the response,

    Actually entity A is connected to fact table with other dimensions (not mentioned for clarity).

    I am having SK in entity A, B and C to track changes (type 2). I do not have business keys in the Primary key and so cannot have the business keys as FKs in the association tables. Am I missing something here?

    Thanks for your help in advance!

  6. #6
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by koi05
    I do not have business keys in the Primary key and so cannot have the business keys as FKs in the association tables. Am I missing something here?
    I was assuming that your database would be Normal Form in which case you could create new tables with the business keys.

    If you are restricting yourself to the Kimball modelling straitjacket then Google for "helper tables" to see what passes for a solution in your world [Sorry - I find it difficult even to discuss that stuff without feeling angry and ill ].

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •