Help in Creating a Movie Database in Access?

Question by shuvo915: Help in Creating a Movie Database in Access?
I want to make a Movie Database With Access. What i Came up with is…
1) a table for movie info (MovieID, MovieTitle, Year, Genre, Director, Actors etc..)
2) a table for actors ( ActorID, Actor, Movies, etc…)
I can’t figure out how to put the actors in the movie table and how to put the movies in the Actors table. And how to make a relation between them.
I am a beginner to databases (haven’t tried anything complex, not good at relationships either). A step by step help would be helpful for me.
Is there any other database program that might be easier than access and does the job better???

Best answer:

Answer by Andrew L
When you design each table, include an ID field. This is an autonumber type field, and Access assigns unique values to it automatically. The first record will be numbered 1, the second record 2 etc. This is usually the primary key of that table (you can assign this using the key symbol in table design). Each name is entered only once in each table.

When you want to link two tables, say Movies and Personnel, you create a third table called Cast (or whatever). This has its own ID, but it also has foreign keys which link it to both Movies and Personnel. It allows you to link one actor to many movies. And one movie to many actors. Also to credit one person several times in the same movie (Mike Myers in Austin Powers).

So your tables might look like this:-
MOVIES
MovieTitle
Year
MovieID (primary key – autonumber field)
GenreID

PERSONNEL
PersonnelID (primary key – autonumber field)
LastName
FirstName
DateOfBirth

CAST
CastID (primary key – autonumber field)
MovieID
PersonnelID
Role
JobID

JOBS
JobID (primary key – autonumber field)
JobDescription

GENRE
Genre ID (primary key – autonumber field)
Genre description

… and so on. The actual links are made using a form, containing combo boxes.

Add your own answer in the comments!