虚构餐厅SQL数据库表关联关系配置技术咨询

看起来你已经搭好了餐厅数据库的基础框架，只是在表关联的逻辑和外键配置上有点混淆。我来帮你梳理清楚业务逻辑，然后修正并完善你的SQL脚本，让所有表都能正确关联起来。

核心业务逻辑梳理 #

首先得明确餐厅的常规流程：

• 客户可以预订餐桌，到店后入座
• 客户下单点选多个菜品
• 用餐结束后，基于订单生成账单结账

基于这个流程，我们需要调整表结构，添加必要的关联字段，并建立正确的外键约束。

修改后的完整SQL脚本 #

DROP DATABASE IF EXISTS restaurantDB;
CREATE DATABASE restaurantDB;
USE restaurantDB;

-- 客户表：存储客户基础信息
CREATE TABLE `customer` (
 `customerID` int NOT NULL AUTO_INCREMENT,
 `firstName` varchar(255) NOT NULL,
 `lastName` varchar(255) NOT NULL,
 `phone` varchar(20) NOT NULL,
 PRIMARY KEY (`customerID`)
);
INSERT INTO `customer` VALUES (1, 'Bruce', 'Lee', '420420589');

-- 餐桌表：存储餐桌信息
CREATE TABLE `table` (
 `tableID` int NOT NULL AUTO_INCREMENT,
 `numOfSeats` int NOT NULL,
 PRIMARY KEY (`tableID`)
);
INSERT INTO `table` VALUES (1, 1);

-- 预订表：关联客户和餐桌，记录预订时段
CREATE TABLE `reservation` (
 `reservationID` int NOT NULL AUTO_INCREMENT,
 `checkIn` datetime(2) NOT NULL,
 `checkOut` datetime(2) NOT NULL,
 `customerID` int NOT NULL, -- 关联预订的客户
 `tableID` int NOT NULL, -- 关联预订的餐桌
 PRIMARY KEY (`reservationID`),
 -- 外键约束：预订属于某个客户
 FOREIGN KEY (`customerID`) REFERENCES `customer`(`customerID`) ON DELETE CASCADE,
 -- 外键约束：预订对应某个餐桌
 FOREIGN KEY (`tableID`) REFERENCES `table`(`tableID`) ON DELETE CASCADE
);
INSERT INTO `reservation` VALUES (1, '2021-09-27 13:24:06', '2021-09-27 14:17:23', 1, 1);

-- 菜品表：存储菜品信息
CREATE TABLE `meal` (
 `mealID` int NOT NULL AUTO_INCREMENT,
 `sides` varchar(255) NULL,
 `main` varchar(255) NULL,
 `beverage` varchar(255) NOT NULL,
 PRIMARY KEY (`mealID`)
);
INSERT INTO `meal` VALUES (1, 'potatos', 'steak', 'wine');

-- 订单表：注意`order`是SQL关键字，改用`orders`避免语法冲突
CREATE TABLE `orders` (
 `orderID` int NOT NULL AUTO_INCREMENT,
 `orderTime` datetime(2) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `customerID` int NOT NULL, -- 关联下单的客户
 `tableID` int NOT NULL, -- 关联用餐的餐桌
 `reservationID` int NULL, -- 可选：关联对应的预订记录（如果是预订客户）
 PRIMARY KEY (`orderID`),
 -- 外键约束：订单属于某个客户
 FOREIGN KEY (`customerID`) REFERENCES `customer`(`customerID`) ON DELETE CASCADE,
 -- 外键约束：订单对应某个餐桌
 FOREIGN KEY (`tableID`) REFERENCES `table`(`tableID`) ON DELETE CASCADE,
 -- 外键约束：订单可关联预订（可选）
 FOREIGN KEY (`reservationID`) REFERENCES `reservation`(`reservationID`) ON DELETE SET NULL
);
INSERT INTO `orders` VALUES (1, '2021-09-27 13:36:06', 1, 1, 1);

-- 订单-菜品中间表：处理多对多关系（一个订单多个菜品，一个菜品被多个订单点选）
CREATE TABLE `order_meal` (
 `orderMealID` int NOT NULL AUTO_INCREMENT,
 `orderID` int NOT NULL,
 `mealID` int NOT NULL,
 `quantity` int NOT NULL DEFAULT 1, -- 记录菜品点单数量
 PRIMARY KEY (`orderMealID`),
 -- 外键约束：关联对应的订单
 FOREIGN KEY (`orderID`) REFERENCES `orders`(`orderID`) ON DELETE CASCADE,
 -- 外键约束：关联对应的菜品
 FOREIGN KEY (`mealID`) REFERENCES `meal`(`mealID`) ON DELETE CASCADE
);
INSERT INTO `order_meal` VALUES (1, 1, 1, 1);

-- 账单表：关联订单，记录支付信息
CREATE TABLE `bill` (
 `billID` int NOT NULL AUTO_INCREMENT,
 `payAmount` decimal(10,2) NOT NULL, -- 改用decimal类型存储金额，支持精确计算
 `payTime` datetime(2) NULL, -- 记录支付时间（可选）
 `orderID` int NOT NULL, -- 关联对应的订单
 PRIMARY KEY (`billID`),
 -- 外键约束：账单对应某个订单
 FOREIGN KEY (`orderID`) REFERENCES `orders`(`orderID`) ON DELETE CASCADE
);
INSERT INTO `bill` VALUES (1, 258.00, '2021-09-27 14:15:00', 1);

关键关联逻辑说明 #

• 客户 ↔ 预订/订单：一个客户可以发起多个预订、下多个订单，所以reservation和orders表都通过customerID外键关联customer表。
• 餐桌 ↔ 预订/订单：一个餐桌可以被不同客户在不同时段预订或使用，所以reservation和orders表通过tableID外键关联table表。
• 订单 ↔ 菜品：这是多对多关系——一个订单包含多个菜品，一个菜品可以被多个订单点选，因此需要中间表order_meal来维护这个关联，同时还能记录菜品的点单数量。
• 账单 ↔ 订单：一个订单对应一个账单（结账时生成），通过bill表的orderID外键关联orders表，这样就能通过账单追溯到对应的订单、菜品和客户。
• 预订 ↔ 订单（可选）：如果客户是通过预订到店用餐，订单可以关联对应的预订记录，方便后续的业务追溯。

原脚本的问题修正 #

1. 外键方向错误：你之前尝试的外键约束方向完全颠倒，比如把table的tableID关联到table的billID——外键应该是在子表中添加字段，关联父表的主键（比如账单属于订单，就在bill表加orderID关联orders的orderID）。
2. 关键字冲突：order是SQL的保留关键字，直接使用会导致语法错误，建议改为orders或者始终用反引号包裹。
3. 字段类型不合理：payAmount用varchar类型存储金额不合适，改用decimal(10,2)可以支持精确的金额计算，避免字符串处理的麻烦。
4. 日期格式不规范：原插入的日期'21-09-27 13:36:06'改为标准的'2021-09-27 13:36:06'，避免数据库解析日期时出错。

内容的提问来源于stack exchange，提问作者Daniel Radosa