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)?


  • 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, 2018 at 15:12

1 Answer 1


Basically you have


Photo : Item

Video : Item

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

table Item
    ItemType -- Photo or Video

table Photo
    Id --> Item.Id

table Video
    Id --> Item.Id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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