Hello, I am trying to find the easiest way to relate tables and fields in my database and am hoping that someone might be able to give me advice. The current example that I am struggling with is as follows:
I have the following tables
- Aircraft Types
- Aircraft Tooling
- Tooling Owners
In this example, I have a Aircraft Tooling Table with over 1000 tools, an Aircraft Type Table with over 20 different aircraft types, and a Tooling Owner Table with a possible of 12 different owners.
Each tool can be used on one aircraft type or several (This is the tool applicability), Each tool can also belong to multiple owners, or owners can own multiple tools.
I know that this is the typical situation for a junction table, but that seems like it would take forever to make all of the entries, and be difficult to manage going forward. It seems like you would need a row for each different circumstance; therefore duplicating a ToolID over and over again to cross it to each aircraft type and owner.
A multivalue lookup would seem to be the easiest way to do this but everyone says not to do this.
Does anyone have any ideas that could help me?
Thanks