Building Film Production Database

I am attempting to build a film production database. Allow me to define:

Tables:

Films
     ID, Name, (other info)
Scenes
     ID, FilmID (which film the scene is from), Scene no., (other info)
Characters
     ID, FilmID, SceneID, Name, Actor (PersonnelID)
Personnel
     ID, Name, (other info)

Etc. That's the basic structure, with a lot more tables than just characters, like props, make-up, etc.

I've built the tables but I'm running into a problem. Each scene should be able to have several characters, props, etc. I assume I would want to make a table as follows:

Scene Items
     ID, FilmID, SceneID (who's lookup is dependent on the selected FilmID), ItemID (like CharacterID, PropID)

The problems are the following: making one lookup dependent on the other (ie SceneID dependent on FilmID) and finding a way to make ItemID attach itself to ALL the item tables( eg. Characters, Props) and select only one. So it might end up like the following for a scene with two characters and a prop:

Scene Items

1     Glass     1     Jenna (in Characters table)
2     Glass     1     Nathan (in Characters table)
3     Glass     1     Nathan's Phone (in Props table)

I suppose I could include a type field...

1     Glass     1     Char     Jenna (in Characters table)
2     Glass     1     Char     Nathan (in Characters table)
3     Glass     1     Prop     Nathan's Phone (in Props table)

But the lookup Item field would still have to be dependent on the type field, which as I proved above, I seem to be lost on. Thanks.Start Free Trial