1

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 projectPhotoDisplayed that holds project.ID and photo.ID(those that are to be displayed only) numbers.
    The bad : too much work, as well as replicating the information that photo table already has (photos associated with projects ).
  • Create a new binary field in the photo table. If true, then display, else not.
    The bad : Structurally does not prevent multiple photos being marked to display. I want radio button behavior
  • Create a new field in the project table that stores the photoID of the photo to be displayed.
    The bad : Does not ensure that the particular photoID is actually linked to this project. 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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
3
  • Thanks! Would you advise an enum field if I needed to store 3 states (thumbnail, displayed, none)? And yes, I meant boolean.
    – Milind R
    Commented Feb 25, 2014 at 8:10
  • @MilindR: Probably yes, sounds like the canonical solution.
    – Doc Brown
    Commented Feb 25, 2014 at 9:36
  • I updated my question.... Is there no possibility of using foreign keys to enforce everything? I don't mind creating new columns.. Just felt this approach isn't clean enough.
    – Milind R
    Commented Feb 26, 2014 at 11:32

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