Db design issue

[mpouzouxis]

Hi All,



I am trying to create a db for movies.

I want to have a main movie table with some basic info and different tables for actors, producers etc.

The end result should be a main form with various sub-forms for actors, producers data entry etc.

Any ideas?

Keep in mind that a movie could have more than one actors, producers, script writers etc.

I tried to make it but no luck so far.

Any ideas?

Thank you

[orange]

What have you done so far?
See this for info on database planning and design.
See this for a generic data model.

[HiTechCoach]

Hi All,

I am trying to create a db for movies.

I want to have a main movie table with some basic info and different tables for actors, producers etc.

Why separate tables for the different roles (positions) a person can have?

I have created a database that handles Movies, TV shows, and Music.

For just Movies the main tables I use are:

Production - Movies, TV show, etc
People - all the info about a person
Production Role - This is a junction table to link people to the Production. Including a field for role (actor, director, etc.) [TIP: Data should not be in table or field names]

Plus some lookup tables for lists like production type, roles, etc.

The great thing about this design is you can view a person and have a sub form that shows all the productions where they have a role. This is easy because there is a single table for roles. If you separate them into different tables then this gets much more complicated.

If you must have the actors listed in a separate sub form from other roles then you would filter the data in each sub form by Role Type field. It does not require separate tables, only separate sub forms and queries.

The goal of a great design should be to keep data duplication to a minimum. This is done by having a single table for all people a person's name is entered only once. A junction table is used to link a person to Movie(s). This also makes it easy to link a person to the same movie more than once (actor, director, writer, etc) without having to duplicate there information in multiple tables.


Let me know if you have any questions.