How do I set up a mySQL table structure for a business directory with many to many relationship?

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 structur开发者_开发知识库e of tables for my database and would be very grateful for any suggestions. I am relatively new to php/mySQL.

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.