none
OTM Relation From Multiple Table into One RRS feed

  • Question

  • Hi,

    In my system I've a few entities (no necessarily related to each other) that need to hold photos, for simplicity, let's name them A, B and C. Due to the fact that it's not possible to add OTM relation between each entity and a "Photos" table and keep all the FKs in a single column, it seems that the following are the ideal options:

    1. To hold a link table ("LINK_TBL") between the entities and Photos table, as follows:

    A OTO -> LINK_TBL OTM -> Photos.

    Each entity will hold the relevant PKs of LINK_TBL, and also PHOTOS will hold the PKs of LINK_TBL. in this case i will be able to create multiple photos for each entity (A,B and C).

    2. To hold a Photo table for each entity with OTM relation, as follows:

    A OTM PHOTOS_A

    B OTM PHOTOS_B

    C OTM PHOTOS_C

    What is preferred? is there a better option that i didn't mention?

    Thanks.

    Saturday, September 24, 2016 8:52 AM

Answers

  • >but in terms of performance

    2) is better

    >future BI needs

    Hard to imagine, but for BI you can always UNION the various photo tables if you really need to.

    >What if tomorrow ... i need to add some attribute to a photo

    Well it's equally likely that you will need to add an attribute to only photos belonging to a particular entity.  And while adding a column to N tables requires you to write N ALTER TABLE statements, adding an attribute to only one entity's photo under #1 requires you to redesign the schema.

    >is this really better to go with approach #2?

    Yes.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by MoranDev Sunday, September 25, 2016 6:32 PM
    Sunday, September 25, 2016 1:11 PM

All replies

  • 2. Is simpler.  So unless you have some strong reason in your domain to need a single table of photos.  For instance you want an A and a B to share the same photo, and only update it once, etc.

    David


    David http://blogs.msdn.com/b/dbrowne/

    Saturday, September 24, 2016 11:20 AM
  • Hi David,

    Thanks for your reply.

    I agree that #2 is simpler and i don't need to share a photo with two entities, but in terms of performance (future BI needs) and maintenance, is this the ideal way?

    What if tomorrow i have multiple entities that need to be linked to photos and each one gets its own table, and i need to add some attribute to a photo, for example, another date or path. i'll have to make this change multiple times (as the number of photo tables).

    What do you think, is this really better to go with approach #2?

    Thanks.


    • Edited by MoranDev Sunday, September 25, 2016 8:28 AM
    Sunday, September 25, 2016 4:19 AM
  • >but in terms of performance

    2) is better

    >future BI needs

    Hard to imagine, but for BI you can always UNION the various photo tables if you really need to.

    >What if tomorrow ... i need to add some attribute to a photo

    Well it's equally likely that you will need to add an attribute to only photos belonging to a particular entity.  And while adding a column to N tables requires you to write N ALTER TABLE statements, adding an attribute to only one entity's photo under #1 requires you to redesign the schema.

    >is this really better to go with approach #2?

    Yes.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Marked as answer by MoranDev Sunday, September 25, 2016 6:32 PM
    Sunday, September 25, 2016 1:11 PM
  • Thanks a lot David,

    Sunday, September 25, 2016 6:32 PM