Post reply

Many to Many relationship

  • August 20, 2009 at 10:41 am

    #209797

    Hi SQL Experts,

    I am designing a database in SQL Server 2005. I need to achieve many to many relationship between 4 tables. If I use the intermediate tables concept, then i need to add few more tables in the database.

    I dont want to create additional tables to achieve many to many relationship. Do we have any option.....other than using intermediate tables concept and achieve many to many relationship.

    Can we use System tables or System views to do this?

    Please let me know a better way to create the many to many relationship without adding additional intermediate tables.

    Thanks.

  • August 20, 2009 at 11:04 am

    #1042288

    To do a many-to-many relationship in SQL you need to create an intermediary table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 20, 2009 at 11:07 am

    #1042291

    The only efficient way to do it is intermediate tables. You can violate normal forms horrible and do it all in one table, of course, but the cost on that is higher than the payoff.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • August 24, 2009 at 10:21 am

    #1043747

    Perhaps what you're looking to avoid is creating 6 new tables to store the relationships between your four tables( a to b, a to c, b to c...). It is possible to use a single table to store the relationships between all four tables. This makes sense only if you're always looking for the total relationship, i.e. data from all four tables. If you're frequently looking for data from just two tables, it may make more sense from a management and performance standpoint to keep more than one table.


    Dan Guzman - Not the MVP (7/22/2010)
    All questions have to be prefaced by Server version and 'according to MS Docs' or 'my own personal opinion based on how much detail I felt like digging into at the time.'

Viewing 4 posts - 1 through 3 (of 3 total)

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