'Belongs to' db relationship best practices?

Question

I'm working on a photography site. Photos on the site will always belong to exactly one event. My initial design was:

Table: Events
ID, Title, etc

Table: Photos
ID, ThumbnailURL, etc

Table: EventPhotos
EventID, PhotoID, SortOrder

This seems natural to me, but I realize the relationship it describes actually allows a Photo to belong to many events. I've thought about refactoring it like this, which would only allow a photo to belong to one event:

Table: Events
ID, Title, etc

Table: Photos
ID, EventID, SortOrder, ThumbnailURL, etc

It seems kind of dirty/messy to me that the event relationship data is in the photo table - the original design separates the photo, the event, and the relationship - but it does avoid a join, and it forces the relationship to a 'has one/belongs to' instead of 'has many' - what do you think?

Answer 1

I can't say I've ever been inclined to use your first option for something like that. I would go with the second one. Way less queries would be used. Simply sorting your query by the SortOrder would work fine as well.

Added: The only reason I would use the first one is if you would (at least at some point) like to make it so photos can be added to more than one album, which doesn't appear to be the case.

Answer 2

For a one-to-many relation, your second take is the standard, normal, correct way of doing it.

However, are you entirely sure that the many-to-many relation you sketched in your first take doesn't apply? I would not be as certain, but you know your domain better than I.

EDIT:

The OP added a comment;

Thanks. I really don't believe photos should ever belong to more than one event - events are photo shoots, each photo is by definition from a certain shoot, and view by event is the only way to browse the site. I'm pretty confident one-to-many is correct.

There are several lessons in that comment, so I wanted to edit this to underline those lessons.

Observation 1. The most important is that you can't model a schema without knowing two things: the real problem domain being modeled, and what our solution requires of the model.

My initial guess was that the OP's events and pictures were modeling the display of photos at events, as in an art gallery (or a gallery of pictures on a web site). In that case, e.g., the Ansel Adams Online Gallery might very well have events, in which Ansel Adams photos are shown, titled "Adams' Mature Work", "Adams's Photos of Structures", or "Adams's Photos of Deserts". All three events might include the Adam's "Transmission Lines in the Mohave Desert", 1941. Since many photos could be shown in many events, we'd need a many-to-many structure.

But the OP's "event" is a photo shoot, and clearly, as the OP notes, any one photo is taken in one and ony one photo shoot.

The lesson here is that we can't model (or can only model provisionally) until we know the Problem Domain.

I'd also suggest that the table name "events" be changed, to make this ore explicit. Calling it a "shoot" or "photo_shoot" makes it more clear what we are modeling.

Observation 2. The OP's comment provides an answer to his objection that having an event FK in a photo is "messy". The OP pretty astutely realizes that this "does avoid a join, and it forces the relationship to a 'has one/belongs to' instead of 'has many'". (Avoiding a join is an implementation issue, the correct relationship is more fundamental, as it is a modeling question.)

What he should also realize is that in our solution, and in the Problem Domain, it makes sense to ask of a photo, "in what photo session/event was this photo taken". And that's why it's not messy: "in which session taken' is a legitimate question about or attribute of a photo, and the FK provides the answer to that question. It also means that in this case, the FK from photo to event shouldn't be nullable: a photo must have an a photo session, or else there could be no photo.

(There are a couple of additional lessons here, about what it means to have a a one-to-many relationship or a many-to-many, and what it means to to make an FK nullable or not; I'll leve those to another time.)

Answer 3

I vote for the second take. There's no need to have a separate eventphotos table.

Answer 4

Hi Palmsey,

Then the second design you have is the cleanest. With the first design, you could use a "has one" relationship between the Photos and EventPhotos tables, but it really has no meaning to separate them out like that. If in the future, you think you need many-to-many relationships, then only at that stage change the design back to the original.

Cheers, Gra.

Answer 5

Any how both the design will cause the one-to-many relation only advnatage you would get in the second was avaoid one more join. But follwing good DB design i will prefer first one. You can think this way if only the photos table needs the relation with some more other tables this time your eventID in this table dont have any role on it.

 For samll level db design i would prefer 2 but for large or medium scale i would prefer the first.

Answer 6

I agree with the others that point out your second option is the accepted method for realizing a one-to-many relationship (one event, many photos). An object-oriented background would lead you to feel that having an event foreign key column in the photo table is an uncomfortable coupling of two separate entities. But from a relational database perspective, that's the way it's done.

The only way to preserve your first choice, while preventing a photo from being used for multiple events, is to place a unique constraint on the PhotoID column of EventPhotos. The relationship between EventPhotos and Photos would then become a one-to-one, which is kinda pointless, but you do gain a decoupling of photos and events.

Your choice, but many years of successful relational database design will still argue for the second option.