How Should I Organize My SQL Tables?

[Robin Hood]

I'm sorry, I'm sure this is a complete n00b question, but I've been working with collecting data, and I'm starting to get to the point where I'd like to start populating a database, but I can't figure out what system will work best.

Final Project:
Dealing with TV Shows and information from the database...

TV Show
- Seasons
- Episodes in season (for episode in each season)
- Information about episode in season

What I Have To Be Able To Do
- Add various other entries into the/a database/table either spanning a certain episode or a whole season (the number of variables for each entry would variate).
- Add/update episodes or seasons for a show

My Background
I haven't dealt with front-end databases much at all; and I haven't done anything complicated with SQL before (I only know how to create databases, tables, columns, and then insert data into those columns).

Questions
(1) Is there a "right" way to do this in terms of performance? In terms of ease-of-use for the programming later?
(2) If there is a right way, what is it? If there isn't, why?
(3) What are the main considerations?

 

[Lock Down]

Well a tall order. Here is a link on relational databases and how to structure them.
http://www.anchor.com.au/hosting/support/CreatingAQuickMySQLRelationalDatabase

Knowing exactly how you want to use the data is the important thing.

Say for tv series , will you search for name, year running, stars , # episodes . name of episoded, ratings and such..

All this will tell you how you want to structure your tables ,how much information in each table and what is it's main use.

Logically if you require certain things every time you query the database like series name, seasons and description.


Check out the link and then figure out the way you want to use the data.

 

[Gaurav]

Make sure you design the tables in such a way that they are normalized -- The crux of the matter being DON'T store values in columns delimited by commas or any other character, rather PREFER using foreign keys and relations so that things are nice and clean

http://en.wikipedia.org/wiki/Database_normalization

Do give that a read for more info (y)

 

[Robin Hood]

First of all, I'd like to thank both of you for linking to some VERY helpful sources (plenty of which was above my head)! =D

I'm still feeling a little lost though. I've read the two sources linked here, and I've also looked at the XMBC wiki on their databases (which, I must say, made me more confused than I thought I was before I looked at it).

Perhaps someone can help me sort this out.

TV Show
- Image
- First Aired
- Genre
- Rating
- Etc.

Then for each show, I want to have (and be able to display) information for each season, and for each episode.

Episode
- Season #
- Episode #
- Episode Name
- Image
- Aired
- Overview

Season
- Image
- Season #?
- Show? (by some UUID?)

This information would be gathered and stored.

These fields/information would be gathered and populated by myself/my system. User posts would then be linked to either an episode OR a season (so a post made for a season wouldn't be linked to an episode). And have it's own set of 2-3 fields.

If I want users to be able to search for a show, see an array of the seasons in that show and the episodes within each season, then go to a particular season or episode post, should I have each of these as separate tables?

Tables: (1) Shows, (2) Seasons, (3) Episodes, (4) Posts
...with each "Seasons" entry having a field linking it to a "Shows" entry, each "Episodes" entry having a field linking it to a "Seasons" entry, and each "Posts" entry having a field linking it to a particular "Seasons" or "Episodes" entry?

Or should I have Seasons and Episodes under one umbrella "Shows" entry.
Tables: (1) Shows, (2) Posts
...and if so, how do I populate that with season and episode entries (I can only really link of a "[[episode for episode in season] for season in show]" kind of thing, which (if I understand correctly), is NOT how I want to do this, but is more...intuitive for myself (likely a sign that it isn't the best way to do this).

It seems like with the first method, more overall data would be involved/used, because:
(1) More fields are required to link everything together.
(2) Each time a complete array is displayed, the SQL table would have to query each entry, combining them into a completed array.
No?

Extraneous Question:
Why does the XMBC database system use such odd columns such as c00, c01, c02, c03, etc.?

 

[Lock Down]

I guess cause they like c (coulumn) then numerically.

Since your knowledge of sql is limited I suggest keep it as easy to understand as possible when naming and creating the database and tables.

First autoincremented ID records should be used where there is no unique way to identify the record.

Next you say you want to give them an array of data with everything everytime. I doubt it because what if they don't know exact name?? What if the want a list of shows for 1979 on abc?

So having only show, year, station and description in the main table might suffice. And image if you intend to keep an image for every show, season, episode like you posted..

If they only wanted a particular episode with a particular star in it?? Will you show that?? The options are endless and the tables should be very selective like the cast table. Not needed for every search. So that would be something as simple as

Show:
id: autoincrement on entry, name, description, starting year, seasons: (how many updated as seasons are entered or removed), station: (bbc,cbs,abc,etc), genre, rating, image

Season:
id: autoincrement on entry, Show#, name, year showed, episodes: (how many updated as episodes are entered or removed), image??

Episodes:
id: autoincrement on entry, Show#,Season#, name, date showed, description, posts: (post ids or topic ids followed by a comma), image??

Cast:
id: autoincrement on entry, Show#,Season#,Episode#, list of cast comma delineated..

Something like this but add whatever you would like or take away..

 

[Gaurav]

The first method is better. Clearly seems normalized at a mere glance. The more number of columns to assign relationships don't matter and the queries are executed faster than you can think about them (y)

Edit: Lock Down was quicker lol :P anyway, as I said, first method ftw!

 

[Robin Hood]

Lock Down,

Thanks so much for the response!!

Made me consider a bunch of factors I hadn't before!

So you're saying when normalizing these tables, one of the goals would be to separate information fields in order to allow the users to search through specific data.

I'm slightly confused by this; (1) is the following true: separating (for example) the episodes table from the seasons table would make searching the database more quickly?

(2) If I wanted to be able to let users sort through all the shows (or shows resulting from their search), it would be better to have season and episode entries separate from the whole season, episode, episode image, episode overview, etc. (how would that work)?

 

[Lock Down]

You are welcome.

It would depend on if they were searching for episode information . No. If they were searching for tv shows for a certain year. Yes.

Updating and inserting. Yes.

Until you get gigabyte sized databases speed will be quick if you are on a well maintained server.

All in all usually tables get corrupted and if you don't have more data in tables than needed you have less hassle getting your site back up in a pure maintenace standpoint.