What kind of tables would I need for the following person database made in MS Access 2003?

Question

It's a database about actors and movies. I want fill it with their age too, so I can answer the queries:

• at what age they made their last movie.
• what is the average age when they made their movies?

etc. These are my proposed tables: Actor table with the fields:

• name
• age
• name of films made
• year of films made

a Film table with the fields:

• name of films made
• year of films made
• names of actors in that film

What would be the best one to use as key?

C. Then I'd probably need a linking table, with the fields:

• Actors
• Films

For all 3 tables: What would be the best field to use as a key?

my follow up question is: What other tables would I need?

And Access 2003 is the only one the client has a license for. Labor of love, volunteer stuff.

Answer 1

To me those would be three distinct tables.

1. Actors:
   • actorTableKey
   • actor name
   • actor date of birth (age can be calculated dynamically by subtracting DOB from current date)
   • actor biography
   • no film listings here
2. Films
   • filmTableKey
   • film name
   • film description
   • film release date
   • no actor listings here
3. The Linking Table
   • actorTableKey
   • filmTableKey

The Linking Table simply lists actorTableKeys and their related filmTableKeys.

Then you can do a groupby actorTableKey in the Linking Table to get a list of filmIDs that correspond to each actor, and conversely groupby filmTableKey to get actors by film.

My Access is a little rusty so I'm not sure of the exact query, but to me that is the cleanest way to do it without having to update the actor table every time you add a film or vice versa.

This is a many-to-many relationship using what access calls a junction table.