Alternative to Many to Many Relationship and Join Tables?

  • 1
  • 2
  • Question
  • Updated 4 years ago
  • Answered

I am trying to relate two tables: Companies and Orders. Orders can have multiple companies and companies can have multiple orders. For example companies are split into four types: Customer, Vendor, Freight, and Storage. One order can have all four types of companies. Can I repeat the same table to table relationship between Companies and Orders 4 times? I've looked into a join table for many to many relationships but that doesn't solve the issue. I would still need to select up to 4 companies within one join table. Any direction is appreciated.

Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb

Posted 4 years ago

  • 1
  • 2
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
I'm not sure I understand why a many-to-many relationship with a join table won't work for you - can you explain with an example?

A "standard" many-to-many relationship with a join table would work as follows:

3 tables:
- Companies
- Orders
- Company-Order Assignment (join table)

Relationships:
1)  1 Company has many Company-Order Assignments
2)  1 Order has many Company-Order Assignments

Say you have:
- Companies:  Customer C, Vendor V, Freight F, Storage S
- Orders: Order 1, Order 2

For Order 1:
- create 4 Company-Order Assignments
1)  Order 1 - Customer C
2)  Order 1 - Vendor V
3)  Order 1 - Freight F
4)  Order 1 - Storage S

For Order 2:
- create 2 Company-Order Assignments
1)  Order 2 - Customer C
2)  Order 2 - Freight F

Within each Company-Order Assignment - you're only selecting 1 Order, and 1 Company.

But you can assign as many Company-Order Assignments as you want - for a particular Order, or for a particular Company.

Here's the online help for Many-to-Many Relationships - which describes the process above (and which I think you looked at).
http://www.quickbase.com/user-assistance/Default.html#many_to_many_relationship.html

Would this work for your situation?   Maybe I misunderstood what you're trying to do.
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
Thank you! This is pretty much what I am trying to do. Though I am relating my Companies directly to my Orders without the join table. However, I only need to have the relationship "Companies have Many Orders" and duplicate that 4 times. I don't need to have an "Order has Multiple Companies" relationship. (The join table is only necessary when the relationship goes both ways right?) I assume this is still the correct way to go based on your example above. What I was ultimately asking was if I could repeat the "Companies have Many Orders" relationship 4 times: once for customers, once for vendors, once for freight, once for storage.
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
You're right, my initial question was confusing. After playing around with it more yesterday I realized that the set up "Orders have Many Companies" was not what I was looking for and that's when I determined that I only need the "Companies have Many Orders" relationship.
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
While you COULD do the "1 Company has Many Orders" relationship 4 times, I actually would still recommend doing the "2-way" many-to-many relationship as laid out.  It'll be easier to maintain, is more flexible, scaleable (for example - what if you had to add a new category, say "Retailer" or something?  In your setup - you'd have to add a whole new relationship)

You may always be concentrating on one side or the other (on Company or Orders) - but the fundamental setup is STILL a many-to-many relationship, and the join table is the standard QuickBase way to handle that.

I'd suggest perhaps creating a new test app - and try setting up the join table as described.  Then you can try out a few scenarios and see how it works.  There are good reasons to do it that way - and we can discuss the pros and cons.  It's easier to have that discussion with a working example in front of you.
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
Ok. I still feel confused by the join table and the many to many relationship but I'll re-read the link you sent and attempt to apply your above example then get back to you with questions. Because you're absolutely correct, there's a high probability that I'd add another category like Retailer in the future. Thanks!
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
I've created an example app with the join table.  Once it's approved and up on the app Exchange (probably early next week) - you'll be able to download it and see how it's set up, and then we can go from there.
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
Here is an example app up on the QuickBase App Exchange - to illustrate an example of the many-to-many relationship between Companies & Orders.

EXAMPLE - Companies & Orders


https://www.quickbase.com/db/main?a=AppDetail&ID=2232


You can go ahead and download it, and see how it works.

In this set up - you can relate multiple Companies to a single Order, and you can see which Orders are related to a given Company. You can also add a new "Type" for a Company, if you want to add additional categories beyond Customer, Vendor, Freight, Storage.

Let me know if you have any questions!
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
Thank you! Yes, this is working out really well. Let me ask a similar question. For each Company we have many Contacts. Should I relate Contacts directly to the Companies table or should I set up a similar many-to-many relationship with a join table as we've done with the Company-Order relationship?
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
If each Contact will only be in 1 Company at one time, then you're fine with "1 Company has many Contacts".  This would work for the majority of cases that I've seen.

If a Contact could belong in multiple Companies at the same time, then you will want to set up a similar many-to-many relationship with a join table.  I'd say this is probably more unusual.
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
Ok, I've been using this many-to-many relationship between my Companies and Orders and the join table "Order relationships" for a couple days. How do I pull information from the embedded report and show it in a new field?  For example, on my orders table, I'd like to create a field that shows the name only of Customer C, Vendor V, and my order date. Can I view this information in another way that is not an embedded report?
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
You can create reports on the join table "Order Relationships":

For example:
- all "Order Relationships", sorted and grouped by Order
- all "Order Relationships" where Type = Vendor
etc.

From the Orders table - you'll see the information as an embedded report, or as a summary field (# of Relationships, # of Customers, etc.).

You may be able to pull some limited data from the child table ("Order Relationships") up to the Order table - but it depends on what you're trying to do.
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
How would I go about pulling this limited data from the child table (order relationships) up to the Order table? In text form vs numeric summary?
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
You would set up a "reverse relationship" - so called because it's the reverse of the normal parent-child table relationship:

- the parent table would be the "Order Relationship" table
- the child table would be the "Order" table

You can search the community posts for "reverse relationship" - one explanation is here in this link:
https://quickbase-community.intuit.com/questions/171124-setting-up-reverse-lookup

Then you can use the "maximum" or "minimum" of the Record IDs - to select the "last" Customer, the "last" Vendor, etc. - and then do a lookup on the reverse relationship to pull it back into the Order table.

What exactly are you trying to do?  e.g. are you trying to display "Customer C - Vendor V - 3/19/2015" on the Order table?  Will that format always be the same?  (always the first Customer, always the first Vendor, etc.)  What happens if you have more than 1 Customer, more than 1 Vendor - on an Order? (or you don't anticipate doing that?)
Photo of Andrea

Andrea

  • 90 Points 75 badge 2x thumb
We will in fact have orders where there could be two customers or two vendors. That would require multiple reverse relationships would it? (Defeating the purpose of our original join table?) Our format is typically "Order # - Date - Customer 1 - Customer 2 (if there is one) - Vendor 1 - Vendor 2 (if there is one.)" But that's just one example of the fields we'd like to reference.
Photo of Xavier Fan

Xavier Fan, Champion

  • 590 Points 500 badge 2x thumb
Yes - it would require multiple reverse relationships.  Or it would require some sort of external script to loop through all the "Order Relationships" to assemble the list of names.

So you have to weigh the pros and cons.  If you go back to doing multiple relationships (to accommodate multiple Customers, Vendors, additional categories, etc.) - then that's somewhat more work to maintain, but then you can create fields such as "Order # - Date - Customer 1 - Customer 2 - Vendor 1 - Vendor 2".

If you use the join table, then it's easier to maintain and run reports in a standard way, but you can only see the full list in an embedded table (or as a link to the list of Order Relationships for a particular Order).