help on a better database design 1

[xhelby]

I am making a database on movies. I created a table for the movies with title, year released and genre as its fields. Then I created 3 other tables each for the cast, director and writer with ID, firstname and lastname as fields for each. I then created a form to enter my data with the movie table as the main form and the 3 other tables as subforms.
My problem is whenever I enter a movie, I kept on entering multiple records of the writer, director and cast. A friend suggested that I put all the people in one table and create another table to specify if the person is an actor, director or writer.
Is there a better design than these? I was also hoping to use forms to enter the data instead of entering all the movies and all the names of the people in tables.
Thanks!

 

[dneufarth]

Try the Video Collection template that comes with Access.

Dave

 

[RickSpr]

The relationship between movies and people is many-to-many; a movie can have more than one person associated with it, and a person can be associated with more than one movie. The standard way of representing a many-to-many relationship is to create a "child" table that holds one key from each of the "parent" tables.

In a fully normalized design, your friend's suggestion is right; there should be a parent table of "people". You would then have the following additional child tables:
- Cast table with Movie ID and Person ID.
- Directors table with Movie ID and Person ID.
- Writers table with Movie ID and Person ID.
For each of these child tables, you may or may not want a primary key. If you want one, you can use the combination of MovieID and PersonID as the key (in other words, the key consists of the whole row). Or, you could add an Autonumber field and make that the primary key. Right now, though, it doesn't seem like you need a key for the child tables.

An alternative, equally good from a theoretical viewpoint, would be a single Movie Person table with a Movie ID, Person ID, and a code field to indicate whether the row represents a cast member, director, or writer.

Which table design you use, and whether you need a primary key, depends on how you're going to use the tables. What you've described so far is fairly simple, and any method would be fine. But suppose some day you decide to keep different information about cast, directors, and writers. If you have them in separate child tables, it will be easier to get it working. So that's what I'd recommend.

When you design your form, you can use one of two methods:
(1) Use a main form based on Movies and a subform based on the child table (or a subform for each one if you want to do them all at once). In the subform, you might have a drop-down combo box for MovieID and another for PersonID to make it easy to enter the data.
(2) Use a single form based on a join between the Movies table and one of the child tables. It gets complicated trying to do multiple child tables this way, though. Also, I'm not sure you could add a movie without adding a cast member, writer, or director this way.

---Some theory about database design---

You don't have to use a fully normalized design, but you should have a very good reason before you consider a denormalized one. A denormalized design is one that contains unnecessary redundant data. For instance, if you were to put a cast member's name into the Movies table, and that person was in many movies, you'd have the name stored many times. If the cast member's name then changed (this is only hypothetical), you'd have a lot of places to change it, and you might have trouble if you missed one. In a normalized design, redundancy is limited to duplicating key fields of parent tables in their child tables (where they are called "foreign keys&quot. Having foreign keys is essential in a relational database; they are the representation of the relations between the tables. You will virtually always have them. That's why I said "unnecessary redundant data," rather than just "redundant data," above.

You can get even less redundancy headache if you create a "magic number" field for the primary key of the parent table. That's what Autonumber fields are really for. Because the "magic number" exists only in the database, there's no reason it ever has to change, so you never have to worry about missing some copies of it. It's not a perfect solution, however. When you use magic numbers in a database schema (table design) with several levels of parent/child tables, it can force you to access tables in the middle (the child table between a parent and a grandchild) even when you don't need anything but another magic number from it. If you use "real world" data for keys, sometimes you can go straight from parent to grandchild, which is more efficient.

Hope this is helpful.
Rick Sprague