Table Size Dividing - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Table Size Dividing

Michael Johnson

2 years ago

I work in the entertainment industry and I'm working on building a database that covers a lot of what I do so it's in one program vs multiple programs. There are 3 main areas of information that I get: Rider Information (what they send us in their riders or emails), Advance Information (what I talk to them about and confirm we are actually doing that day), and Show Report (report of what actually took place). There are many different areas that I get these bits of information in (lighting, sound, staging, crew, etc.). As I'm designing my tables, I'm wondering about the pros and cons of keeping all the information (rider, advance, and show report) in one table for each area. For example, I currently have an audio table, a lighting table, staging table, etc.. I've been going back and forth between splitting those to be 3 tables each (Rider, Advance, Show Report), or at least 2. Any thoughts on downsides to keeping it as 1 table for each area?

	Sami Shamma @Reply 2 years ago
In my experience, keep similar information together in one table. Richards advocates that too. In several videos, he talks about how it makes sense to have one "PersonT" instead of ParentT, StaffT, StudentT. So, of your three table hold mainly similar information, then your best option is to have them in one table.

	Michael Johnson OP @Reply 2 years ago
That's pretty much what I've been thinking. The only thing I can think of that may be an issue is when a show is going on, and I've got one person editing the show report and other changes need to happen in the Advance area. This would only be for multi-day events, but I can see that scenario happening on occasion.

	Sami Shamma @Reply 2 years ago
you know your application best. you have not shared enough information for us to give you more advice.

	Kevin Robertson @Reply 2 years ago
If you can see a scenario happening my advice is to take steps to prevent that scenario. If extra tables prevent errors in your database it's really not a bad thing. Of course we would need to see the structure of your Tables to advise you further.

Michael Johnson OP @Reply

2 years ago

Thanks Sami. It's always a balance of sharing enough information vs information overload. I could easily (and have a tendency to) give a lot of details, so I have to reel myself in and share what's relevant, then share more as needed. And thank you Kevin. I think I may just need to have it in the wild to see if it really will be an issue. When I'm ready to deploy it, it'll be a soft launch anyway, with us doing double entry in both the old and new system to make sure we're covered if any errors or issues come up.

Kevin Yip @Reply

2 years ago

As long as tables are normalized, whether you should have split tables or combined tables is often based on needs and circumstances. For instance, to store names of a film's cast and crew, you may want to have split tables for the various departments: a directors table, writers table, actors table, producers table, etc. But what if someone is both a director and a writer or actor? His name will appear in multiple tables, and that is a violation of the normalization principle, which says that every item in a database can only appear once in the database. In this case, split tables for cast and crew won't work.

	Michael Johnson OP @Reply 2 years ago
Yeah, it's normalized. One example is, I ask if the tour is using their own audio console, one of our consoles, or if we need to rent a specific console. In the Rider section, I have it as a short text to enter what was on their rider. In the Advance and Show Report sections I have them as drop-down menus. So there is the same item, but treated differently based on the section.

	Sami Shamma @Reply 2 years ago
Michael, we do not understand your terminology. the only common language between us on this forum is Access. use terms like Table, Query, field, form etc. Here is an example of your post: "one person editing the show report and other changes need to happen in the Advance area" I do not know what is "editing show report" means. what is the "Advance area"

	Michael Johnson OP @Reply 2 years ago
Hi Sami. I'll try to do better in the future. You all were able to answer my question. Thanks.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View