I have a personal photo album application where I upload my photos. However, I'd now like to extend it with videos as well, and I'm unsure how to properly perform this extension.

The current design consists of 2 tables - Albums table that contains info about the albums (name, dateCreated, etc.) and Photos table, where each record is referencing one of the albums and contains info about the picture (fileDestination, name, description, photoRotation, position etc.). Note that each record also holds information of this photo's order/position within the album.

I have 3 proposals as to how the extension could be done

  1. Add "type" filed - The most straightforward solution. I'd simply add a new field which stores what type of photo/item the record represents. Disadvantage is that it feels "dirty" since, for example, field rotation does not make sense for a video (I allow rotation of photos, but not videos). In future should I want to include new album item types, there may be more fields necessary for an item which would then be unnecessary for e.g. photos.
  2. Create "Video" table - Creating a separate table for videos. The problem with this solution is that it has almost identical design to Photos table (missing only the "rotation" field) and when repositioning items within album, I'd have to reorder items in 2 tables. Regarding repositioning, this also scales badly and is not "future-proof" since adding n new tables means reordering items in n new tables.
  3. Create "AlbumItem" table - The table would have Id, Position, AlbumId fields. The advantage of this solution is that in future I can add as many new album item types (in form of new tables) as I'd like to. The disadvantage is that I have to migrate the current setup with all photos to this new design.

Which design do you think is the most appropriate (theoretically and/or practically)?

Thanks

  • if you want to show photo/video togehter in the same gallery-view it is much easier to have both in the same table. the android build in media database does this where all media types are in the same table plus a type field distinguish video from photo from audio. there are also database-views for image, audio and video that select the apropriate media-file colums with an additional "where type=xxx) – k3b Aug 8 at 15:12
up vote 1 down vote accepted

Basically you have

Item
    AlbumId

Photo : Item
    PictureFile

Video : Item
    VideoFile

And your database should reflect that. The only question is really does Video have all the same fields as Photo. If not:

table Item
    Id
    ItemType -- Photo or Video
    AlbumId

table Photo
    Id --> Item.Id

table Video
    Id --> Item.Id

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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