I'm not sure if multi-tier design is the appropriate terminology, but here's an example of what I'm trying to accomplish.
I'm building a database to track information about a children's television show.
At the top-most level, will be general information that is specific to that episode (air date, episode title, episode name, etc...).
I have a table for that.
At the next level, will be repeatable bits of information about that particular episode (a welcoming scene, a craft, a game, etc...).
I started out with a table of generic scenes, but many episodes will have scenes that are only specific to one episode and I thought that table would become unnecessary.
I was already going to require a one-to-many table to join the episode to the scenes, so I just used a normal text field for the scene name instead of referencing another table (I can run a distinct query for a dropdown list of choices or they can free-type).
At the next level, under scenes, would be the actors in scene.
I have a table of actor names an a one-to-many table to join the scene to the actor (as one actor may be in multiple scenes and we need to be able to track who was in what).
At the next level, under actors, would be the characters that the actors played.
I had a table of character names, as a single actor could be multiple characters and some characters require multiple actors or can be played by different actors (puppets, for example).
Like the scene table, I removed it and just added a text field for what character was played by what actor in scene-actor table.
Each episode has a music scene and each music scene has multiple songs.
I have a table with a listing of songs and a one-to-many table joining the songs to that music scene.
We also need to track who performed during that music scene, so they are also listed as actors and their respective positions (bass player, lead singer, etc...) as characters.
Here is a generic visualization of what I'm talking about...
As mentioned, I removed the characters table in favor of just putting that information in the scene-actor join table, but it seems like it really should be it's own dataset.Code:Episode | ——Scene | ——Actor | ——Character ——Song | ——Actor | ——Character
That would require another level of join table and this is where it's starting to get foggy.
I haven't been able to wrap my head around how to only show that character that was played by that actor in that scene of that episode (and even moreso, who was the drummer for that song in that scene of that episode).
To make matters more annoying, while the band members don't change song-to-song, the lead singer can be different for different songs in the same set.
Instead of just being able to determine the actors for the music portion, it seems like I would have to define them for each song (or, at least, the lead singer).
I've been talking about a top-level, table, design, but the same will hold true for queries and forms (especially, data-entry forms).
I'm trying to think about what will become too time-consuming for someone to add information.
They would have to see a form for the general episode information, then click a button that opens another form to enter a scene, then click another button to open another form to enter the the actors for the scene, then click another button to open another form to choose their character.
It just seems unnecessarily complicated, but it may just be the nature of the beast.
Hopefully, I'm just missing something that will declutter the whole thing and make life easier for everyone.
Thanks...
JJ