1

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

2
  • the first thing that came to mind was, where do you store multiple trailer, but your second schema resolved it. Allow N'ULL for all column that can not be filled all the time.
    – nbk
    Nov 23 '20 at 15:47
  • The question of how many columns is "too many" is somewhat subjective. 40 columns suggests you might look for a better design, but does not prove that your design is bad. Normalization deals mostly with problems that arise at update time, not at retrieval time. There are reasons other than normalization to sometimes decompose a table. Nov 23 '20 at 19:53
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.

1
  • 1
    Thank you for your reply! It made me think of a third scenario, so I created another diagram and updated my post. I think it's a nice blend. What are your thoughts?
    – AJ Tatum
    Nov 23 '20 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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