Home Forums SQL Server 2012 SQL 2012 - General One to Many relations RE: One to Many relations

  • September 30, 2016 at 8:16 am

    #1904164

    moranamon (9/30/2016)

    Hi Leo,

    Thanks for your reply.

    With your permission i would to explain again approach #1.

    Three entities, let's name them all.

    Employees, Houses, Cars. lets assume that each one of them should have at least one photo and they are not related to each other at all.

    Now the structure should look as follows:

    Employees <- (oto) Link_Tbl -> (otm) photos

    Houses <- (oto) Link_Tbl -> (otm) same photo table

    Cars <- (oto) Link_Tbl -> (otm) same photo table

    Note the the PK of Link_Tbl should be stored in each relevant entity as FK and not the opposite, look at the arrow direction.

    And the same PK of Link-Tbl will be stored in Photos as well.

    this is how each record in one of the entities can contain multiple photos that will be stored in photos, using the connection of Link-Tbl.

    I'm attaching a small photo that demonstrate it much better.

    Thanks

    Are you sure you need a link table? 20 photos of one employee could be stored in the photos table as 20 rows each having the same EmployeeID.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden