moranamon (9/21/2016)
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?
Option one has one definite disadvantage and that is knowing which photo relates back to which entity, unless you specifically use different PK values on the three entities. Another way of putting this is if A and B both have PKs 1,2,3,4 and each has 1 or more photos, then a join between A and the link table will return photos from both A and B.
Using a single table would then require another mechanism to distinguish related entity. I would think having three link tables would be better from this perspective.
There's a 3rd option:
A - OTM-A --
B - OTM-B -- Single Photo_Table
C - OTM-C --
Again this way you would need a way to identify the relationship of the photo back to a specific entity to resolve the join issue mentioned above.
My personal recommendation - 3 entireties, 3 link tables, 3 photo tables.
Leo
Nothing in life is ever so complicated, that with a bit of work it can't be made more complicated.
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.