Generating a cast list for a television show via MySQL

[suttercain]

Hi guys,

 

I am putting together an episode guide for various television shows and would like to include the cast and the character they portrayed in that episode.

 

What do you suggest the would be the best approach to do this before I enter all the data?

 

Example:

CAST (column)

Tom Welling

Kristen Krusiek

Michael Rosenbaum

How would set it up so I could display it as:

Tom Welling... Clark Kent

Kristen Krusiek... Lana Lang

Michael Rosenbaum... Lex Luthor

 

Could I just separate the data in that cell like this and use explode?

Tom Welling, Clark Kent - Kristen Krusiek, Lana Lang- Michael Rosenbuam, Lex Luthor-

 

What would you suggest to be the most efficient way of doing something like this?

 

Thank you for you time.

[Psycho]

To do it the right way would require a few tables such as:

 

Episodes (contains basic episode info)

Actors (contains a list actors - only one record per actor)

 

Episode_Actors (this table would link actors to episodes as well as identiry their character name). Something like this:

 

episode_id, actor_id, character_name

 

 

 

Because of the fact that the same actor plays the same character in a series you could also have separate tables for series and episodes. Then you could assign actors and characters to the series and then just assign that to each episode.

[suttercain]

I do have more columns... my current mySql table

 

COLUMNS:

show            (Smallville, Justice League, etc.)

episodeTitle  (Rapture, Cruise Control, etc.)

season          (1, 1, 2, etc.)

episode        (1, 4, 16, etc)

body            (episode description, plot outline)

airDate          (2007-01-02, 2004-04-12, etc)

image          (smallville.jpg, jla.jpg, etc)

cast            (HOW SHOULD I ENTER DATA?)

 

Thanks for the response.

 

[Psycho]

In my opinion you should NOT enter the cast data into the table above. Cast to Show is a many to one relationship. That is the whole point of a relational database. You create a separate table for the "many" items and link them to the "one" item.

 

At a minimum you could add an id field to your table above. Then create an actor table with the following columns:

 

show_id

actor_name

character_name

 

But, that would be inneficient because you would need to create many duplicate records, which is why made the previous suggestion.

 

 

[suttercain]

Hi mjdamato,

 

Thanks for your reply. I thought about the relational table, but as you said, that to would be inefficient.

 

Does anyone know of an efficient way to do this?

 

Thanks.

 

SC

[Psycho]

I already gave you the efficient way to do it which would require several tables, not just two. You never want to define the same data twice.

 

So, based upon some of the data you have above I would have the following tables:

 

SHOWS

show_id

name

image

 

EPISODES

episode_id

show_id (FK)

title

season

episode

body

airdate

 

ACTORS

actor_id

name

 

ROLES

role_id

actor_id (FK)

character

 

EPISODE_ROLES

episode_id (FK)

role_id (FK)

 

Or alternatively you could assign roles on the show level.

[suttercain]

Hi mjdamato,

 

Thanks for the reply. I apologize, I have never used foreign keys, so I was a bit confused. I now see what you're saying and I have a quick question for anyone who can help:

 

If I UPDATE or INSERT one table, does it also INSERT or UPDATE the corresponding table(s)?

 

Also, if I make a form so I can enter this information via a web browser, do I just use JOIN to insert the information?

 

 

Thanks again!

 

SC

[Psycho]

Well, it is much easier to pull the data from the database with all the records linked, but it is a little different when updating records. Here is an example of how I might handle the updating of records.

 

Let's say I want to add an Episode for the show Friends. If I do not currently have any Friends episodes in the database I would first have a page to add the Show.

 

Then I would go into an add episodes page. On that page I wuld have to select the show that it is associated with via a select list generated from the SHOWS table. Then after entering in all the relevant information I would need to add the actors. I would construct a GUI that would allow me to find and add 1 role at a time, e.g. Ross (David Schwimmer). If the actor/role combination I want is not already in the database I would need to create it. That would require an add actors/roles page.

 

The add actors/roles page could actually e broken into two pages, but I would only do one. The page shoud allow you to add an actor if he/she doesn't already exist. Then it would also allow the user to add a role to any existing actor.

 

So, my advice would be to start with the pages needed for adding shows, then move to episodes. Then you will be ready to take care of the actors/roles pages.

[suttercain]

Thank you for this great advice. I set up the tables last night and will try this method via forms. Thanks again! I'll let you know how it goes.

 

Peace