well, you started off pretty well, just went off the rails a bit toward the
end there. comments inline.
<gn********@gma il.comwrote in message
news:2e******** *************** ***********@k24 g2000pri.google groups.com...
I was going to design a simple movie database, but I'm just stuck in
the very begining.
It will contain the following information.
Country, Movie, Plot, Actors
So, I created tables like below.
tbl_Countries ( countryID, Country_Name )
okay.
>
tbl_Movies ( movieID, Title, Year_Released, countryID )
good, including the foreign key countryID from tbl_Countries.
>
tbl_Actors ( actorID, Name, DateOfBirth, countryID, MovieID )
whoa, here's where we start to hear that scary screeching sound. you put all
the countries in one table, and the fields in the table describe a specific
instance of a country (in this case, just ID and name). you put all the
movies in a table, and the fields in the table describe a specific instance
of a movie (the country that the movie was made in - or released in,
whatever - is descriptive of the movie, too). so put all the actors in one
table *where all the fields describe a specific actor*. countryID fits the
bill, IF you're talking about the country the actors was born in, or lives
in, whatever; the point is that the country info in this table must describe
an actor. but a movieID, that doesn't describe an actor at all, so it
doesn't belong in this table.
you have to analyze the relationships between the real-world entities. one
movie may include many actors (of course), AND one actor may act in many
movies (unfortunately, in some cases!). there's the definition of a
many-to-many relationship, right there. in Access, a many-to-many
relationship is represented by using a linking table, with each of the
primary tables having a one-to-many relationship to the linking table, as
tblMovieActors
MovieActorID (primary key)
movieID (foreign key from tbl_Movies)
actorID (foreign key from tbl_Actors)
the relationships are:
tbl_Movies.movi eID 1:n tblMovieActors. movieID
tbl_Actors.acto rID 1:n tblMovieActors. actorID
and there you go. each record in tbl_Movies describes one instance of a
movie; each record in tbl_Actors describes one actor. each record in
tblMovieActors describes a specific actor in a specific movie. does the
movie have 10 actors? this table will have 10 records for that movie, each
one including one of the 10 actors. did so-and-so actor make 18 movies? the
table will have 18 records for that actor, each one including one of the 18
movies.
suggest you read up/more on relational design principles. for more
information, see
http://www.accessmvp.com/JConrad/acc...abaseDesign101.
hth
>
Now the problem with my design above is that it would require me to
add multiple records of same actor if the actor starred in many
movies.
How should I design this database? I'm so hopeless.
