Database normalization question

[FooFighter]

I was going to make a database to store a list of my DVD's. I have a question about the table structure though.

I want to have some fields for actors. I'm thinking 4 or 5 would be plenty. From what I understand it wouldn't be proper to have fields like Actor1 Actor2 and so on. As I would want to be able to search for a movie based on an actor, how would I structure my tables for this and keep things normalized? I was thinking perhaps another table with actor names, but then on the main table I'd still need several fields to hold each actor. Or maybe I need one field for actors where I'd simply put in all the names I wanted? Wouldn't that also be against normalization rules since I could possibly make typos which would introduce bad data?

[ck9663]

This are basics of database design.

Read this


--- CK

[FooFighter]

OK I'm totally confused after reading that article lol.

From the article I clearly see it's bad to have Actor1 Actor2 etc etc on my Movies table.

When I search for the movie I want it to display 3 or 4 of the leading actors plus other data such as rating and running time. The actors bit is what confuses me though as it's bad mojo to have Actor1 Actor2...

I see I could also have a problem with the Actors table. Actor1 could be in movie1, movie 4, movie 76 bla bla bla. Dang this is confusing.

I'm more then sure the answer is very simple, I'm just not getting it. How the heck does a place like NetFlix do their tables?

[FooFighter]

Is this the right path?

OK I have the following table structure. Is this considered normalized?

dbo.Movies
MovieID - int - PK - IsIdentity (true)
MovieName - varchar(100)
RatingID - int - FK to Rating table
ActorID - int - FK to Actors table
MoviePic - varchar(50) - stores path to picture file ie. \movie.jpg
Description - text
GenreID - int - FK to genre table
Year - varchar(4)
RunningTime - varchar(7)

dbo.Actors
ActorID int PK - IsIdentity (true)
LastName - varchar(50)
FirstName - varchar(50)

Didn't make the genre or rating tables yet. Just wondering if I'm going in the right direction to have things normalized here. Now I'm confused on how I'd go about listing more then one actor per movie without blowing the normalization rules all to hell. If I wanted to show 3 main actors for each movie it seems like I'd have to input the same movie 3 different times. errrrrrr Or maybe I should add a MovieID FK to the actor table to show which movies he's in? Then I'd have to list the same actor several times to account for each movie he's in. OMG my mind is mush now lol. Is database normalization always possible or in some cases just a wet dream?

[balame2004]

Hell,

Your database had one to many enty mapping. But you need to have many to many mapping structure. Because one movie may have many actors and one actor may acted in several movies. So you need third entity to make relationship between movie and actor.

I recomend the following table structure that will suite your project.

dbo.Movies Table
MovieID - int - PK - IsIdentity (true)
MovieName - varchar(100)
RatingID - int - FK to Rating table
MoviePic - varchar(50) - stores path to picture file ie. \movie.jpg
Description - text
GenreID - int - FK to genre table
Year - varchar(4)
RunningTime - varchar(7)

dbo.Actors Table
ActorID int PK - IsIdentity (true)
LastName - varchar(50)
FirstName - varchar(50)

dbo.MovieXActor Table
RelationshipID - int - PK - IsIdentity (true)
MovieID - int - FK to Actors table
ActorID - int - FK to Actors table


Yo can easily relate movie and actor by creating a record in MovieXActor table.