Hi I have data that I need to store in my database. My website is about tv-shows, and the data I’m talking about is basically seasons and episodes. My concern is about whether I should use two tables or one. I’ll make myself more clear:
Option 1:
seasons_table
- post_id
- season_number
- season_title
- language
- subtitles
- item_date (when it was created)
- item_modified (when it was last modified)
episodes_table
- post_id
- season_id
- episode_number
- episode_title
- item_date
- item_modified
Option 2:
unique table
- post_id
- item_type (season or episode)
- season_number
- season_title
- language
- subtitles
- season_id
- episode_number
- episode_title
- item_date
- item_modified
I can already see for myself that with Option 1 there’s gonna be a lot of common fields between the two tables, while with Option 2 there’s gonna be a lot of fields that are never gonna be used (e.g. an episode will never have a value in the field season_title since it just needs a value for season_id to be linked to that season).
So which one is the best option? I’m willing to choose option 2, but I’m worried that those empty fields are gonna waste memory or loading time or whatever while processing any data in that table. Is that true? Thanks in advance to everyone, I hope I made myself clear.
By the way my website is wordpress based and I’m gonna use a custom table, but I think i’m gonna use some wordpress functions to process data like $wpdb->insert and so on…
Two tables is the best approach here, that more closely adheres to the rules of database normalization. If there’s concern about duplication you need to better evaluate where you’re storing data.
item_dateanditem_modifiedare probably unique for each entry even if they are “duplicated” in terms of schema. Don’t worry about this.Whatever
post_idis, you’ll have to evaluate if you have a direct relationship between post and these two tables, or from post to seasons to episodes.