how do i design a dvd collection database

[Guest]

OK, this is what I have so far from John V.:
Movies
MovieID Autonumber Primary Key ' links tables together
Title
Medium <DVD, VHS, CD, Super-8 film, 8-track tape...>
Studio
Rating <G, PG, ...>
IssueDate
PurchaseDate
...

Actors
ActorID Autonumber PrimaryKey
LastName
FirstName
<any other desired bio information>

Cast
MovieID ' what movie does this actor play in
ActorID ' who's in the cast
Role ' what part did they play

Keywords
Keyword <e.g. Adventure, Comedy, etc.>

MovieKeywords
MovieID
Keyword

How would I deal with not only listing the entire cast for each movie, but
also having multiple movies featuring the same actors? My ideal database
would be searchable my cast.

 

[tina]

John's setup does support mutiple movies with the entire cast of each, *and*
mutiple movies with the same actors.

you have a Movies table and an Actors table. they have a many-to-many
relationship: one movie may have many actors, and one actor may be in many
movies. to resolve a many-to-many relationship, you create a "child" linking
table that has a many-to-one relationship with each "parent" table. that's
the Cast table. the relationships are
Movies 1:n Cast
Actors 1:n Cast

you enter multiple records in the Cast table - one record for each actor
that acted in each movie. if Robert Redford acted in three of your movies,
then he's listed in the Cast table three times - once for each movie, as

Cast
MovieID ActorID
The Sting Robert Redford
Out of Africa Robert Redford
Sneakers Robert Redford

(of course the Cast table will contain the primary key values from Movies
and Actors - not the name values.)

My ideal database
would be searchable my cast.

not sure just what you mean. if you want to be able to search for an actor's
name in table Cast, and get back all the movies that actor appeared in - you
can do that with a query, or by filtering a form.

hth