Postgresql – structuring media uploads (users, images, videos) using sequelize.js

database-designpostgresql

(using pseudocode to simplify)

say I have a User (users table)

User {
  id: PK
}

and I want to have them upload images and videos (media). I want to be able to differentiate between images or videos but they have overlapping fields beyond their media types. so that I can query for all of their media, or just videos or images.

I am unsure of the best way to structure this. here are two options I came up with

Option 1: use media table with a media_type column (enum values video and image)

Media {
  id: PK
  media_type: enum (image, video)
  uploader_id: FK (users.id)
  url: VC
  length: Int (null for images)
}

sequelize:

  • user.getMedia(); // get all media
  • user.getMedia({ where: { media_type: 'image' } }); // get images
  • user.getMedia({ where: { media_type: 'video' } }); // get videos

I could also use the scope (on media_type) and alias (Images, Videos) options on the Model.associate to create getMedia(), getImages(), getVideos() methods.

Option 2: separate images and videos tables

Image {
  id: PK
  uploader_id: FK (users.id)
  url: VC
}


Video {
  id: PK
  uploader_id: FK (users.id)
  url: VC
  length: Int
}

sequelize:

  • user.getVideos();
  • user.getImages();

So my questions are:

  • which option (of the above 2 or another you have) is preferred and why?
  • if option 1 is chosen does it become unmanageable if I add more media types?
  • how do I do getAllMedia() for the second option? The only thing I can think of is to write a custom prototype method that queries each table and reduces them (in order of upload date). this seems incorrect.
  • say I also keep track of all the User's likes from the media they view. option 1 would allow me to keep the media.id FK on the likes table. option 2 appears to lead me to image_likes and video_likes which seems redundant.

Best Answer

I am leaning towards Option 1 for this because I actually think that as you add more media types option 2 is going to get unmanageable when it comes to querying, especially when you are trying to get all of a user's media, it would just require too much eager loading of the other tables, or a lot of join tables.

With option one, each piece of media has it's own unique id, so if you set up the association between Users, Media, and Likes so that: Likes belongTo users Users can hasMany Likes Media can hasMany Likes Likes hasOne media (so each individual like is a representation of it's userId and the mediaId it was associated to)

then you would be able to query your Likes table for all Likes with a certain userId (to show all the media a user has liked), or for all Likes for a certain mediaId (ex. to show the amount of likes for a specific image).

You wouldn't even necessarily have to specify the media type when querying likes because you'd have your mediaId or UserId depending on what you are trying to query.

Related Question