Overall Organization Question

[Pete F]

So I posted a question a while ago about timers, and in that same thread I mentioned the project I was working on. It involves keeping track of the progress of a film shoot, where each time the camera begins to record, I am adding information about what the camera sees. I will do my best to keep the technical jargon of filmmaking out of this description, but if I lose you at any point, let me know.

I have a couple organization-related questions that I was hoping I could discuss with you all. Since I began working on the project I have tried to create my database in a few different ways using several tables and relationships, but nothing seems to be perfect yet. Hopefully someone could give me advice on the best way to structure the tables and relationships or explain why I am going about it wrong. I'll start by giving you a general idea of how my system works.

At the start of my day on a film set, I would add a new record in a table for "Shoot Days", which included info about what time we start filming, eat lunch, finish for the day, etc.

After that I would find out that what scene we would begin filming. So in my "Scene" table I would add a record with info about the scene, mostly text, nothing complicated.

Now that I knew what scene we were going to be filming, I would add a record to yet another table called "Cameras". Sometimes we would film a scene with 1 camera, ore times with as many as 4 cameras. It varies on every movie. So for this example, I will say that there are two cameras. I would add two records to this table, with information about what lens they are using, what the camera is focused on, etc.

Finally, once we would be close to actually filming, I would add a record to a table called "Takes" where I would begin to fill out information as the cameras started filming.

Now what I would like to have is a series of layout that go from one table to the next. So once I finish the new record for today in the "Shoot Day" table I could jump to the "Scene" table and automatically have the scenes that I add throughout the day be linked to the one "Shoot Day". The same goes for each subsequent table, so that when I reach the "Takes" table, I know that each record can be traced back to the original "Shoot Day". Now if I were to go to a higher table than "Takes" and add a new record or switch to a previous record that needed updating (maybe we decided to add more footage to a scene previously started), each table related child table would reflect that change.

In the end what I want in a report would look similar to a simple spreadsheet with a few formatting tweaks here and there. I would include every bit of data from each "Take" record along with the data from each table in the hierarchy that it was linked to.

Now here is where I got lost. Should I have each layout referencing a unique table, similar to the way I have described already? Or should I have created a single table with every field from the tables I have already discussed and have each layout edit only part of the "Mega-Table" where each new record from any given layout duplicates the information from the previous layouts but allows me to edit the current layouts information?

I know this might seem simple to some of you out there who are masters of database creation, but I am just starting out, and any and all information is helpful. I am still learning about table occurrences and table occurrence groups, so if there is a method to this madness that I have not even thought of, I would love to hear it!

Thanks to everyone who reads this!

-Pete