Need Help Normalizing a Database for Media Website

Question

I've created a media website where visitors can watch trailers, view pictures, etc. Along the way, I've found that I've needed additional images for sharing, etc. I went the "easy" route and grew the "Media" table to something that's become somewhat ugly. I can continue to grow the Media table, but I know it's bad design to have a table that has too many columns.

Here's the gist/diagram of the current schema (the Media table is shortened to include only relevant columns, it has about ~40 columns total): https://dbdiagram.io/d/5fbbcf413a78976d7b7d1252

I thought about "peeling" away at the Media table by creating a MediaVideos table as I'd like to have a trailer and also a short clip (that would be available just for registered users). However, I stopped as I quickly realized that If I were going to essentially rearchitecht things, I should do it the right way.

So, I'm thinking of creating two new tables: MediaAssets and MediaAssetTypes. Something like this: https://dbdiagram.io/d/5fbbd2513a78976d7b7d12e8

The issue, I feel, is that means the table would have some columns that wouldn't be used for some assets. Is that ok? For example, I only need the height & width for some images/assets, not all.

I just want to get a second (or more) opinion on this before I essentially tear apart my application to get it right.

Any suggestions & ideas are welcomed!

Thank you!

UPDATE: After thinking it further and thinking about J.D.'s reply, I've thought of a third scenario where I keep the MediaImages table but add a Height, Width, and ImageTypeId to support images that I need to know the height/width of. And then create a MediaTrailers table with a TrailerTypeId: https://dbdiagram.io/d/5fbc21363a78976d7b7d1d87

Answer 1

You should read up on Star Schema and Database Normalization. While it's not always the solution for all database designs by any means, I believe following a loose implementation of it is generally good practice in a lot of cases, including yours. (It's even a derivative of what the whole Snowflake database management system is based off of.)

Here's a pretty good document on Star (and Snowflake) schema design: Understand star schema

(While this is targeted towards PowerBI, I think Microsoft does a good job explaining the concepts of the schema designs, so just focus on those sections.)

This is a good straightforward article on Normalization by Microsoft: Description of the database normalization basics

Database Normalization is even more relevant to your design question and more important between the two to understand.

From looking at your first design with the Media table that has 40+ columns, it might make sense to break it into different tables for the things that begin with the same prefix in your column names. E.g. TrailerAttributes and SocialMediaAttributes could be their own tables. Then things in your Media table that don't have a trailer for example, won't have an empty and unneeded column. You can further normalize things away into their own tables this way too, say for example some common property belonged to both some Trailers and some Social Media rows. You'd make a further decoupled table that stored those attributes shared among the other tables. (This is another lever of normalization, which will make sense after you read my link above.)

It seems like your updated schema is a step in the right direction, especially with aiming to implement a more generic solution.