Jump to content
  • 0

Troubles with a many to many relationship



I have a database that has a table that lists businesses. One of the rows is "category" which ties to a "categories" table. I want to have businesses be able to have more than one category, and obviously a category can have more than one business (many to many). I've tried creating a "third" entity that is business_categories, but I can't figure out how to make it store each business's category and also how to pull from/use the separate table in a datapage (datapage seems to only allow pulling data from one table at a time). Thanks for any help.

Screen Shot 2019-02-21 at 8.11.16 AM.png

Link to comment
Share on other sites

2 answers to this question

Recommended Posts

  • 0

 Hi @MattS814,

You should store the list of categories in the Categories table. In the  B
usiness_categories table, you should have the information about what category related to what business. To make the information updated in the Business_categories table, when there are changes in the Categories table of the existing records, you can Enforce referential integrity and Cascading Update related fields. Please follow this link for more details: https://howto.caspio.com/tables-and-views/relationships/relationship-settings/

If you want to pull up the information from another table, you can use Select statements in the Calculated fields or in the Calculated Values. Please follow this link for more details: https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/
Also, you can create a view to join tables and pull the information from some table in one datapage. Please follow this page for more details: https://howto.caspio.com/tables-and-views/what-are-views/creating-a-view-to-join-tables/

Link to comment
Share on other sites

  • 0

Hi @MattS814,

Your table structure and relationship is already good, it just needs some modification. What I did is I created three tables for this (Business table, Category table, and Business Category Table), the Business Table contains all the information about the business entity, the Category Table contains all the information about all the category that a business can have. And the third table is the   Business Category Table, which serves as a conjunction table between the Business Table and Category Table.


On my third table, I've set the primary key of the two tables(Business Table and Category Tables ) as a foreign key in this conjuction table while having its own primary key.  By doing it this way, we can capture/assign almost every category available to a business using this conjuction table 


I've also attached the exported copy of these tables so that you can review it on your end.

I hope this helps.





Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...