如何为具有多对多关系的业务目录设置mySQL表结构？

Question

I am trying to plan my table structure for a business directory where I will have multiple parent categories for multiple sub-categories and multiple business listings, all of which will require a many to many relationship.

eg.

A Night Out

Clubs and Societies
Public Houses
Restaurants
Taxis and Private Hire Vehicles
Theatres and Concert Halls
Wine Bars

Restaurants

Chinese / Oriental
Bistros
Fish and Chips
Indian
Italian
Public Houses
Seafood
Spanish

Transport and Carriers

Air Charter and Rental
Airlines
Car / Van Hire Self Drive
Courier and Distribution Services
Estate / Property Re-location Services
Freight Services and Agents
Haulage Contractors
Post Offices and Services
Re-location Services
Taxis and Private Hire Vehicles
Van Hire

As you can see, Restaurants is both a main category and a subcategory. Taxis and Private Hire Vehicles belongs to both Transport and Carriers and A Night Out and under all of these categories, on the lowest level, I will have the business listings. I will not be allowing businesses to submit their own listings but rather adding them myself upon request. A business listing can belong to a maximum of 6 categories.

I am struggling to find the best structure of tables for my database and would be very grateful for any suggestions. I am relatively new to php/mySQL.

Answer 1

You could have a category table which will contain a list of all possible categories/subcategories and then in your information table have it so that there is a CategoryID field as well as a SubCategoryID where you will either have NULL for the subcategory or the corresponding id.

---

You could also map it using a mapping table, that way you will be able to have as many categories / subcategories as you want.

Category Table ( CategoryId, CategoryName )
Business Table ( BusinessId, BusinessName )
Category Mapping Table ( BusinessId, CategoryId, IsCategory )

The IsCategory would be a BIT field so that you can mark any number of categories for a business as either a SubCategory (0) or a Category (1).

On the Category Mapping Table I would make the PRIMARY KEY ( BusinessId, CategoryId ) as well as make them FOREIGN KEYs to the Business / Category table respectively.