How to structure this MySQL data?

h0ly lag · 09-20-2009, 08:50 AM

Ok, so I am making a script for one of my friends. He is creating lists of movies with their info, such as the poster, a description of the movie, etc... I got this working fine. But he is also listing TV shows with the same generic information. What it's about etc.. Then he has links to each episode. Now my question is, how do I organize the tables in the database to be able to add the episodes to the TV show series, and then bring all this together and be able to have each episode become a clickable link when I put the page together that will read from the database?

This is what I'm thinking:

-Database

--tv
---ID
---series title
---Description

--episodes
---ID
---parent series
---episode name
---description
---more fields, etc..

JaoudeStudios · 09-20-2009, 09:53 AM

It depends on how generic you want to be between movies and tv shows.

So the schema you propose is that for tv show only?
If so it looks ok, I would change 'parent series' to be tv_id

h0ly lag · 09-20-2009, 07:41 PM

Ahh yes, I already have the movie table taken care of. And yes, the parent series would be according to the tv_id. But I have another question, because each series is going to have different amounts of episodes, how will I deal with the amount of columns I am going to need for each show. Oh crap, I just remembered that it will need to be sorted by season as well. So 'Tv_show > Season > Episode.'

Ok, but mainly how do I deal with the amount of columns that I will be needing? because not every show is going to have the same amount of episodes. Should I just be leaving the extra ones blank?

tony · 09-20-2009, 08:41 PM

It seems like a hierarchy structure, although season doesn't have a lot of data to put on it from the looks of it, so I will go with a 1-to-many relationship between shows > episodes and just make season a field to episodes. Something like this:

Code:

-shows
--show_id
--name
--description

-episodes
--episode_id
--show_idfk
--name
--season
--description
--more fields, etc..

the show_idfk field in the table episodes would help to make the relationship with the shows table (fk stands for foreign key).

h0ly lag · 09-21-2009, 07:32 AM

Thanks tony, I think that will work out very nicely. :)