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 (
dateCreated, etc.) and
Photos table, where each record is referencing one of the albums and contains info about the picture (
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
- 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
rotationdoes 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.
- 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
nnew tables means reordering items in
- Create "AlbumItem" table - The table would have
Id, Position, AlbumIdfields. 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)?