I have two tables, project and photo. photo has ID,projectID,path : each project has multiple photos.
Now one of these photos is the one that has to be displayed. The question is how to represent this in the database.
Earlier, I used descending order of ID numbers and chose the latest one, but that failed and caused problems. Now I am not sure which way to go :
- Create a new table
projectPhotoDisplayedthat holdsproject.IDandphoto.ID(those that are to be displayed only) numbers.
The bad : too much work, as well as replicating the information thatphototable already has (photos associated withprojects ). - Create a new binary field in the
phototable. If true, then display, else not.
The bad : Structurally does not prevent multiplephotos being marked to display. I want radio button behavior - Create a new field in the
projecttable that stores thephotoIDof thephototo be displayed.
The bad : Does not ensure that the particularphotoIDis actually linked to thisproject. Also does this information belong here? - Any approach with foreign keys so that referential integrity is maintained..?
Using PHP and MySQL InnoDB database, though I’m sure it won’t matter.
Which is the best way to store this info?
All the three ways are viable solutions, but I would avoid the first one and the third one because of redundant information which could get “out of sync”. There could be a photoID stored as foreign key of a photo which belongs – or is moved to – a different project.
Create a new binary field in the photo table.
You surely meant a boolean field, not a binary field. This would be my favorite option. To make sure there will be at maximum one photo to be displayed, you may add “before update” and “before insert” triggers to your DB, counting the numbers of rows where your “display” column has the value “true” and throwing an error if there would be more than one. Here you find an example how to use “before insert” triggers for verifying constraints.
3