One to Many relations
-
Hi,
I've a few different entities that need to hold photos (one or many), 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.
-
If you want to change a photo in one place, do you want the photo to change in all places that it's used?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A single photo can be linked to one entity always.
So if i want to change it, it should get changed in one place, which is linked to one record in one of the entities.
the thing is which of the mentioned ways are preferred and if there is a better way.
Thanks.
-
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. -
Hi,
Regarding the 3rd approach, I didn't understand why do i need to use 3 link tables if i have 3 Photos tables.
If each entity has a unique Photo table, so we can use OTM relation.
PhotoA table will hold the PK (guid) of each entity in A and there is no corelation between A & PhotoA to the others.
Regarding the first approach,
If A holds the PK of the LINK_TBL (A OTO LINK_TBL) and Photos holds the PK of LINK_TBL (LINL_TBL OTM Photos), i can easily know which photos are related to which directory.
Is it a bad approach?
Please advise...
-
As I understand your first option you had 3 Entities (A, B, C) and a single link table, but you didn't define any table schemas.
Lets assume each entity has a PK, int identity(1,1) The rest doesn't matter here.
This means each table will have PKs 1,2,3,4,5,.... etc.
The link table will then have columns
Entity-PK int -- Points bask to the entity
Count-Val int -- To maintain the 1-many relationship
Photo_Location
Under this model you can have the situation where the link table has rows
1,1,Path_A11
1,2,Path_A12
1,3,Path_B13
Where Path_A photos are from entity A, and Path_B photos are from entity B.
the query: SELECT E.PK, L.Count_Val, L.Photo_Location from Entity_B E join LinkTable L on E.PK = L.Entity_PK
will return photos from Path_A and Path_B which is wrong, it should only have returned Path_B13 !
You therefore need another column to identify which entity the photo relates to, or some other system such as different identity rangers at the entity level.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated. -
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
-
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 ShawFor 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 -
Yes, but what about other entities?
you can't store FKs from difference entities in the same column in Photos, that's the reason why i created the link table from the first place.
The question is if my first scenario is better/worst than creation a unique photo table for each entity.
Thanks.
-
moranamon (9/30/2016)
Yes, but what about other entities?you can't store FKs from difference entities in the same column in Photos, that's the reason why i created the link table from the first place.
The question is if my first scenario is better/worst than creation a unique photo table for each entity.
Thanks.
-- Actually, you could, if the datatype for Employees,
-- Houses and Cars are compatible with each other.
-- For Employees
SELECT *
FROM dbo.Employees e
INNER JOIN dbo.Photos p
ON p.ParentID = e.EmployeeID
AND p.ParentTable = 'Employees'
-- or, say, p.PhotoSource = 'E' - a cheap CHAR(1) NOT NULL
-- and for Cars
SELECT *
FROM dbo.Cars c
INNER JOIN dbo.Photos p
ON p.ParentID = c.CarID
AND p.ParentTable = 'Cars'
-- alternatively a bridge table as others have suggested
SELECT *
FROM dbo.Employees e
INNER JOIN dbo.EmployeesPhotos ep
ON ep.EmployeeID = e.EmployeeID
INNER JOIN dbo.Photos p
ON p.PhotoID = ep.PhotoID
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor 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 -
I would use an individual photo table for each parent with a 1:M relationship. Using a link table adds a layer of complexity for your application developers that isn't needed. I seem to recall that there's a relationship rule (I can't remember the canonical verbiage) that a child record (the photos) should only have the parent key value and an additional identifier for uniqueness, plus whatever else they need to store (the image or its storage location). Adding an additional field to identify the table doesn't seem cricket: the relationship should identify the parent.
And I think having them in individual tables would probably reduce the possibility of lock contention where multiple table updates are banging against the same table of photographs.
I see no advantage having all your photos in one table when three parents exist to feed it, with or without a link table between them. I think having three photo tables with (probably) identical definitions and indexing won't represent a significant increase in overhead versus one photo table to hold them all.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Are you sure this will never be a many to many relationship, even if it is not one now? A person can appear in more than one photo but can a photo show more than one person? The same applies to cars and houses etc.. can they all appear in the same photo? Only you can answer that, in which case the idea I have is a link table.
Something like
<
photoID(fk),
entityID(fk),
entityType([house| person| car])
>
In either method you are creating three joins but this may help by avoiding a final union all by having all the relationship data already in one table.
If indeed a 1 to many relationship then the above is really the core of your photo table (except that photoID will be a PK). It is less maintenance as you only have to define entity types on top of the new entity table... rather than build a new photo table to house these as well (like if you decide you want to start photographing birds and bikes). Just try to consider the future as well as the present.
----------------------------------------------------
-
MMartin1 (10/4/2016)
Are you sure this will never be a many to many relationship, even if it is not one now? A person can appear in more than one photo but can a photo show more than one person? The same applies to cars and houses etc.. can they all appear in the same photo? Only you can answer that, in which case the idea I have is a link table. ... Just try to consider the future as well as the present.You bring up a good point. If the database stores photos internally in tables, that's one thing and I could see an argument to reduce storage requirements (though I'd still argue for separate tables for a more sensible design). If it's just a pointer to an image stored on disk, the overhead would be pretty small to have multiple pointers to the same image.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font] -
Hi,
Actually, it'll never be MTM as each photo predicts a unique indicator. as for the location, i don't store the photos in the DB but on remote storage (Azure) and just keep the path.
My issue with an individual photos table for each entity is that if tomorrow i'd like to add an additional property to Photo table, something that is useless at the moment and might be useful in the future, something like DateOfChange, i'll have to perform the change for all the photo tables (number of entities).
Is it correct?
-
moranamon (10/6/2016)
Hi,Actually, it'll never be MTM as each photo predicts a unique indicator. as for the location, i don't store the photos in the DB but on remote storage (Azure) and just keep the path.
My issue with an individual photos table for each entity is that if tomorrow i'd like to add an additional property to Photo table, something that is useless at the moment and might be useful in the future, something like DateOfChange, i'll have to perform the change for all the photo tables (number of entities).
Is it correct?
Yes, having a separate photo table for each entity type means that will be the case. A suggestion - If you develop an entity table , you can have the entityTypeID in the photo table. Especially if you person table and your cars table have IDs that overlap. But that means that the entityType will be in more than one table for the same entity, photo and person for example.
This is a case where you instead can employ something that Kimball calls a heterogeneous schema. The idea is although people and cars are entities, they have different sets of properties that describe them.
EntityTypes table
--------------------
entityTypeID (pk)
entityTypeName
Entities table
-----------------------
entityID (pk)
entityTypeID
(below are your extention tables)
entityPersons table
-----------------
entityID (pk)
firstname
lastname
dob
etc
entityCars table
--------------
entityID
make
model
year
etc
Now you have the setup to have
Photos table
--------------
photoID (pk)
entityID
LinkString
etc
----------------------------------------------------
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply