我正在尝试创建一个使用PHP和MYSQL(与XAMPP一起)的数据库,其中存储有关各种快餐店及其分支机构的信息,以及他们在分支机构提供的菜单。我已经在我的数据库中创建了一些表,详细说明了快餐店、分支机构、菜单的类别和子类别以及它们在表中提供的项目。下面是表的摘录/样例:
Restaurants
+-------------+----------------+
| RestaurantID| RestaurantName |
+-------------+----------------+
| 1 | PizzaHut |
| 2 | BurgerKing |
| 3 | KFC |
| 4 | SubWay |
+-------------+----------------+
Branches
+---------------+----------------+
| RestaurantName| BranchAddress |
+---------------+----------------+
| KFC | XXX-1234 |
| KFC | AAA-1234 |
| KFC | YYY-1234 |
| BurgerKing | DDD-1234 |
+---------------+----------------+
Categories
+---------------+----------------+
| BranchAddress | Categories |
+---------------+----------------+
| XXX-1234 | Fries |
| AAA-1234 | Burgers |
| YYY-1234 | Drinks |
| DDD-1234 | Burgers |
+---------------+----------------+
SubCategories
+---------------+----------------+
| Categories | SubCategories |
+---------------+----------------+
| Fries | Cheese |
| Burgers | Chicken |
| Drinks | Carbonated |
| Burgers | Beef |
+---------------+----------------+
Items
+---------------+----------------+
| SubCategories | Items |
+---------------+----------------+
| Cheese | CheeseFries |
| Chicken | Zinger |
| Carbonated | Pepsi |
| Beef | Whopper |
+---------------+----------------+
如上所示,每个餐厅都可以有多个分店,每个分店可以有多个类别,每个类别可以有多个子类别,然后每个子类别中可以有多个项目。分店可能具有相同的类别和/或子类别,但其中的项目不同。
在这种情况下,我认为集中式的类别、子类别和项目表不适用,因为每个餐厅的每个分店都是其自己独立的实体,需要其自己的一组类别、子类别和项目表。如果是这样,我该如何创建表,并设置必要的外键呢?
Categories
+----------------+
| Categories |
+----------------+
| Drinks |
| Burgers |
| Fries |
| SetMeals |
+----------------+
SubCategories
+----------------+----------------+
| Category | SubCategories |
+---------------+-----------------+
| Drinks | Carbonated |
| Drinks | Carbonated |
| Drinks | Carbonated |
| Drinks | Non-Carbonated |
| Burgers | Beef |
| Burgers | Beef |
| Burgers | Chicken |
+----------------+----------------+
SubCategories
+----------------+----------------+-------------+
| Category | SubCategories | Items |
+---------------+-----------------+-------------+
| Drinks | Carbonated | Pepsi |
| Drinks | Carbonated | Root Beer |
| Drinks | Carbonated | Cola |
| Drinks | Non-Carbonated | Orange Juice |
| Burgers | Beef | Whopper |
| Burgers | Beef | Cheese Burger|
| Burgers | Chicken | McChicken |
+----------------+----------------+--------------+
当然,我的想法也可能是错误的,对于这种情况而言,中心化表格可能会更为适用。如果是这样,请提供一些示例或指导,以便设置这些表格。
我考虑了以下解决方案:
Restaurants
+-------------+----------------+
| RestaurantID| RestaurantName |
+-------------+----------------+
| 1 | PizzaHut |
| 2 | BurgerKing |
| 3 | KFC |
| 4 | SubWay |
+-------------+----------------+
Branches
+---------------+----------------+
| RestaurantName| BranchAddress |
+---------------+----------------+
| KFC | XXX-1234 |
| KFC | AAA-1234 |
| KFC | YYY-1234 |
| BurgerKing | DDD-1234 |
+-------------+------------------+
Categories
+----------------+----------------+----------------+
| RestaurantName | BranchAddress | Category |
+----------------+-----------------+----------------+
| KFC | XXX-1234 | Fries |
| KFC | AAA-1234 | Burgers |
| KFC | YYY-1234 | Drinks |
| BurgerKing | DDD-1234 | Burgers |
+----------------+----------------------------------+
SubCategories
+----------------+----------------+----------------+----------------+
| RestaurantName | BranchAddress | Category | SubCategories |
+----------------+-----------------+---------------+----------------+
| KFC | XXX-1234 | Fries | Cheese |
| KFC | AAA-1234 | Burgers | Chicken |
| KFC | YYY-1234 | Drinks | Carbonated |
| BurgerKing | DDD-1234 | Burgers | Beef |
+----------------+---------------------------------+----------------+
Items
+----------------+----------------+----------------+----------------+-------------+
| RestaurantName | BranchAddress | Category | SubCategories | Items |
+----------------+-----------------+---------------+----------------+-------------+
| KFC | XXX-1234 | Fries | Cheese |CheeseFries |
| KFC | AAA-1234 | Burgers | Chicken |Zinger |
| KFC | YYY-1234 | Drinks | Carbonated |Pepsi |
| BurgerKing | DDD-1234 | Burgers | Beef |Whopper |
+----------------+---------------------------------+----------------+-------------+
尽管上述表格为每个餐厅及其分店提供了各自的类别、子类别和项目,但这违背了最初拆分它们的整体目的,而且相当愚蠢。编辑:我试图对上述表格进行反规范化,以便可以使用查询来查询它。
$query="SELECT * FROM Items WHERE Restaurantname='BurgerKing' AND
BranchAddress='XXX-1234' AND Category='Fries';
$result=mysqli_query($cxn,$query) or die("Error");
while($row=mysqli_fetch_assoc)
{
extract($row);
echo "$Item";
}
注意:我计划将数据库扩展到包括非快餐餐厅甚至非餐饮相关的商店,因此各种表格(类别/子类别和项目)必须能够包括来自非餐饮相关商店的类别(例如电子产品/书籍等)。
非常抱歉问题很长,希望能得到任何建议和帮助。
谢谢!