I am still working on a photo storage app (designing the database for the data for the photos stored), using MySQL, and have been told that a photo may have one or more people in it, and a person may be in many photographs, which makes sense.

My issue is in understanding the properties of the linking table between the photo and person tables. I have set up a linking table with the PK of both tables (Photo and Person) as the composite PK for the linking table. Both sets of PK in this table are set to Not Null (by me) but the project then stipulates that people can be in the database even if they aren't in a photo, and I was wondering how this tallies with the NN setting of the composite PK. Surely if it has to be NN, this means it can't allow for there to be any entries where I just have the PK of the photo and none for the person part of the linking table? Or am I totally misunderstanding how these relationships work? As mentioned previously, I am in the early stages of learning about databases.

  • Or would one treat the relationship between each of the two tables with the linked table as a one-to-many relationship, and therefore even if there is an entry in the parent Person table, there does not have to be an entry in the linking table if there is no associated photograph in the database? – zv426 Jun 27 '20 at 14:09

Actually you don't need a special value for people that aren't there

When you select a photo andand you don't know if there aren't any people in it, you make

SELECT p.*,pe.*
FROM Photo p 
   LEFT JOIN reftable r ON p.id = r.p_id 
   LEFT JOIN peope pe ON  r.pe_id = pe.id 
WHERE p.id = 123

So even when there is no entry in the Relationship table, you get an photo.

If you have one person and want to find all photos that they are in

SELECT pe.*,p.*
FROM peope pe  
   INNER JOIN reftable r ON r.pe_id = pe.id
   INNER JOIN Photo p  ON p.id = r.p_id  
WHERE pe.id = 123

You need here a relationship table, because many persons could be on a photo or one person can be in many photos

  • Ok, thanks. So what you're saying is it is possible for this to happen? If there aren't corresponding entries in both tables, then it just would result in no entry in the linking table, is that what you're saying? We've only just started working on left and right joins this week so we wouldn't be making use of this at this stage, but I just wanted to ensure that this is a possible situation to have – zv426 Jun 27 '20 at 17:59
  • You should build an example database and plaay arounf with uit, pe and ponply need an id and a identifying text. so you can see the result, but as mentioned when here is no entry in the relationship table it doesn't really matter, with the different JOINS you have some possibilitys to get data you want. – nbk Jun 27 '20 at 18:12
  • Thanks, that's really helpful – zv426 Jun 28 '20 at 15:16
  • very good, please accept the answer – nbk Jun 28 '20 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.