By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,625 Members | 1,274 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,625 IT Pros & Developers. It's quick & easy.

Simple DB Design Question

P: n/a
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 )

tbl_Movies ( movieID, Title, Year_Released, countryID )

tbl_Actors ( actorID, Name, DateOfBirth, countryID, MovieID )

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.
Nov 18 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Change your tables a small bit and you got it!

TblCountry
CountryID
CountryName

TblActor
ActorID
ActorFirstName
ActorLastName
DateOfBirth
CountryID

TblMovie
MovieID
Title
YearReleased
CountryID

TblMovieActor
MovieActorID
MovieID
ActorID

You need a main form based on TblMovie to enter movies and a subform based
on TblMovieActor to enter all actors in that movie.

Steve
<gn********@gmail.comwrote in message
news:2e**********************************@k24g2000 pri.googlegroups.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 )

tbl_Movies ( movieID, Title, Year_Released, countryID )

tbl_Actors ( actorID, Name, DateOfBirth, countryID, MovieID )

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.

Nov 18 '08 #2

P: n/a
well, you started off pretty well, just went off the rails a bit toward the
end there. comments inline.
<gn********@gmail.comwrote in message
news:2e**********************************@k24g2000 pri.googlegroups.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.movieID 1:n tblMovieActors.movieID
tbl_Actors.actorID 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.

Nov 18 '08 #3

P: n/a
Wow, thank you very much Tina and Steve.
That have helped me a lot.

Thanks again.
Nov 19 '08 #4

P: n/a
you're welcome :)
"gnewsaccess" <gn********@gmail.comwrote in message
news:41**********************************@35g2000p ry.googlegroups.com...
Wow, thank you very much Tina and Steve.
That have helped me a lot.

Thanks again.

Nov 19 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.