如何为具有多对多关系的业务目录建立mySQL表结构？
我正在尝试为业务目录规划我的表结构，其中我将有多个子类别和多个业务列表的多个父类别，所有这些都需要多对多关系。
例如。
外出一晚
俱乐部和社团
酒馆
餐厅
出租车和私人出租车辆
剧院和音乐厅
酒吧
餐厅
中式/东方 小酒馆
鱼和薯条
印度
意大利语
酒馆
海鲜
西班牙语
运输和承运商
包机和租赁
航空公司
汽车/货车租赁 自驾
快递和配送服务
屋苑/物业搬迁服务
货运服务及代理
运输承包商
邮局和服务
搬迁服务
出租车和私人出租车辆
货车租赁
正如您所看到的，餐厅既是一个主类别，又是一个子类别。出租车和私人出租车辆属于运输和承运商和夜间外出，在所有这些类别下，在最低级别，我将拥有企业列表。我不会允许企业提交自己的列表，而是根据要求自己添加它们。一个企业列表最多可以属于 6 个类别。
我正在努力寻找适合我的数据库的最佳表结构，并且非常感谢您的任何建议。我对 php/mySQL 比较陌生。
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.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您可以有一个类别表，其中包含所有可能的类别/子类别的列表，然后在您的信息表中包含它，以便有一个 CategoryID 字段以及一个 SubCategoryID，其中子类别或相应的 id 为 NULL 。
您还可以使用映射表来映射它，这样您就可以拥有任意数量的类别/子类别。
IsCategory 将是一个 BIT 字段，以便您可以将企业的任意数量的类别标记为子类别 (0) 或类别 (1)。
在类别映射表上，我将分别创建主键（ BusinessId、 CategoryId ）并使其成为业务/类别表的外键。
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.
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.