Troubles with a many to many relationship

[MattS814]

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.

[Alison]

 Hi @MattS814,

You should store the list of categories in the Categories table. In the  Business_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/

[TsiBiRu]

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.

Regards,

TsiBiRu

 

Tables_2019-Mar-04_1758.zip