none
Many to many with Timerange RRS feed

  • Question

  • I hava a many to many relation where a dimension member is only valid between a period.
    ex. Client A is in relation with sales person from 2002/1/1 until 2003/4/2 after the client is in relation with another sales person,...

    My current solution now is adding a record in the bridge table for each day with the salesperson & client link and this is working. However I was wondering if there is an alternative that I kan work with a from & to date so I don't need to insert a record every day in my bridge table?

    Thx

    Wednesday, October 3, 2012 10:35 AM

Answers

  • That approach sounds good to me. I personally would store it as one row then put a view on top of the table to return one row per day in the date range. The other option is to chain multiple many-to-manys together. Your current bridge table could return 20020101588 as DateRangeKey representing 2002/01/01 for a duration of 588 days. Then you could have a second many-to-many which returns one row pr day in that date range. The user won't know they're using cascading m2m under the covers. If performance is suffering with your approach you might try cascading. Marco Russo describes cascading m2m here: http://www.sqlbi.com/articles/many2many/

    http://artisconsulting.com/Blogs/GregGalloway


    Thursday, October 4, 2012 8:19 AM
    Moderator

All replies

  • That approach sounds good to me. I personally would store it as one row then put a view on top of the table to return one row per day in the date range. The other option is to chain multiple many-to-manys together. Your current bridge table could return 20020101588 as DateRangeKey representing 2002/01/01 for a duration of 588 days. Then you could have a second many-to-many which returns one row pr day in that date range. The user won't know they're using cascading m2m under the covers. If performance is suffering with your approach you might try cascading. Marco Russo describes cascading m2m here: http://www.sqlbi.com/articles/many2many/

    http://artisconsulting.com/Blogs/GregGalloway


    Thursday, October 4, 2012 8:19 AM
    Moderator
  • Thanks Furmangg I will definitely check that out! thx for the option with the view to!
    Thursday, October 4, 2012 9:02 AM