Connecting 4 tables, best solution?

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
There are 4 tables all interacting with one and other. I will describe them from the bottom up:
1) Documents - this will house all documents that are referenced in the various next 3 tables. The hope is that a document can be related to multiple tables at the same time, without creating multiple records of that document.

2) 3rd party components table - this table tracks pieces of a whole material that belong to a whole material (say the handle bars on a bike).

3) Materials Table - This table tracks records of a whole materials (say a bike) that are utilized for their own record keeping purposes and in the next agreements table. This record needs to be able to show multiple related 3rd party components from table 2.

4) Agreements table - this table tracks agreements (contracts) under which the organization running the database shares the materials in table 2. The hope is that you can add multiple materials to an agreement record, and pull related data from 3rd party components>>materials>>agreements.

I need a material record (in table 3) to show all 3rd party components that "apply" to the material, which would be indicated by the user in some manner. That meaning the third party component is part of the whole material, but needs to be tracked separately because the components could apply to multiple different material records. I need to be able to add multiple 3rd party components (and some lookup fields from the 3rd party components records, if possible) to a material record.

I need an agreements record to be able to reference multiple material records, because an agreement can contemplate several materials. I'd like the agreements record to be able to pull up any third party components info from those associated material records as well.

Overall, I need the app to be able to connect each record that has a mention of the other by some indication. When I explained this to QB, it was suggested I start a many to many relationship table. Unfortunately, from my understanding, you can still only relate one record to multiple, but not multiple to multiple. (Please excuse me if this is wrong).

Is it easier to do this by using embedded reports based on a textual field in the respective records as opposed to the "creating a relationship" method? Can this be done effectively with the creating a relationship method? Should I be looking at custom formulas and scripts to produce these types of results? Would appreciate any input!
Photo of Andrew

Andrew

  • 192 Points 100 badge 2x thumb

Posted 8 months ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
When you want true tracking on a multiple levels you have to create a 'joined table'  (for whatever reason, QB people always say 'many-to-many' which is not correct).

So you would need to create the master tables and the joined tables.  

...just brainstorming...
You would need the following tables..

Clients
Agreements
Materials/Items
Components
Material /Item Components (Joined table)
Documents

Relationships:
Client --> (has many) Agreements
Client --> Materials
Clients --> Documents

Agreements --> Materials
Agreements --> Documents

Materials --> Material Components
Materials --> Documents

Components --> Material Components
Components --> Documents

Material Components --> Documents


This is just a rough guess based on what you described.  Honestly I'd guess you have a lot more moving parts that you are trying to track, so I'm sure we are missing things.
Photo of Andrew

Andrew

  • 192 Points 100 badge 2x thumb
Thanks, Matthew. You are correct I was guided to utilize a Many to Many, but I find that this is restricted to 2 tables referencing one and other. I'll have to look up more information about a joined table, and how to implement in QB. Any suggestions for literature? Also, in your initial analysis, do you feel my goal is possible without creating a large number of joined tables that feed into larger joined tables?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
The quickbase university is a good resource on these items.

As far as you being able to do it without a bunch of joined tables.  I think it should be relatively straight forward and not too many. 

But, i don't know your whole story.  Nobody's app is usually as simple as above.  That is only part of the story for one problem/question.

You might need to work with your QB account manager to truly evaluate/design the app.
Or
Trial and error - build it and it it doesn't do what you need, try again.
Or
Talk with a pro ;)



Matthew Neil - Product Specialist