点餐系统数据库设计（MYSQL）

Database design for food ordering system (MYSQL)

提问by DreamCrusher

I need some help designing the database of a food ordering system.

我需要一些帮助来设计食品订购系统的数据库。

Customer
Customer_id (PK)
Customer_name
Customer_phonenum
Payment_method (Cash/Credit Card)
Payment_status (Paid/Unpaid)
Email_address
Employee_id (FK)
OrderF_id (FK)
OrderD_id (FK)

Customer
Customer_id (PK)
Customer_name
Customer_phonenum
Payment_method (Cash/Credit Card)
Payment_status (Paid/Unpaid)
Email_address
Employee_id (FK)
OrderF_id (FK)
OrderD_id (FK)

Employee
Employee_id (PK)
Employee_name
Employee_position
Employee_age
Employee_phonenum
password

Employee
Employee_id (PK)
Employee_name
Employee_position
Employee_age
Employee_phonenum
密码

Food
Food_id (PK)
Food_name
Food_size
Food_price (Describe price of each food)

Food
Food_id (PK)
Food_name
Food_size
Food_price（描述每种食物的价格）

Drinks
Drink_id (PK)
Drink_name
Drink_size
Drink_category (which explains hot/cold)
Drink_price (Describe price of each drink)

Drinks
Drink_id (PK)
Drink_name
Drink_size
Drink_category (解释热/冷)
Drink_price (描述每种饮料的价格)

OrderFood
OrderF_id (PK)
Customer_id (FK)
Employee_id (FK)
Food_id (FK)
Quantity_item
Total_price

OrderFood
OrderF_id (PK)
Customer_id (FK)
Employee_id (FK)
Food_id (FK)
Quantity_item
Total_price

OrderDrinks
OrderD_id (PK)
Employee_id (FK)
Drink_id (FK)
Quantity_item
Total_Price

OrderDrinks
OrderD_id (PK)
Employee_id (FK)
Drink_id (FK)
Quantity_item
Total_Price

1. How should I make it in such a way that a customer can order a different type of food with a different quantity in the OrderFood and OrderDrinks tables?

2. Should I repeat the Food_price and Drink_price in OrderFood and OrderDrinks respectively?

3. For employee password, how should I limit the length between 6-15 characters? (I thought of using varchar, but it also allows 1 char to be the employee's password.)

1. 我应该如何使客户可以在 OrderFood 和 OrderDrinks 表中订购具有不同数量的不同类型的食物？

2. 我应该分别在 OrderFood 和 OrderDrinks 中重复 Food_price 和 Drink_price 吗？

3. 对于员工密码，我应该如何将长度限制在 6-15 个字符之间？（我想过使用 varchar，但它也允许 1 个字符作为员工的密码。）

回答by TheBrokenSpoke

1) Fisrt, remove the foreign keys from the customer table, and the purchase fields. Then create an orders table similar to this

1）首先，从customer表和purchase字段中删除外键。然后创建一个类似于这个的订单表

Orders
    id
    CustomerId (FK)
    EmployeeId (FK)
    PurchaseMethod
    PurchaseStatus
    TimeStamp
    TotalPrice

Then you would create an association table that links your order to the food and drink items. With this table, you can link as many food items to an order as needed.

然后，您将创建一个关联表，将您的订单链接到食品和饮料项目。使用此表，您可以根据需要将尽可能多的食品链接到订单。

FoodOrders
    id
    OrderId (FK)
    FoodId (FK)
    Quantity
    TotalPrice

Create a similar table for your drink orders.

为您的饮料订单创建一个类似的表格。

The totals columns would have to be calculated when an order is placed.

下订单时必须计算总计列。

2) Only repeat the price data if it is necessary for your business process. Best methods are to not repeat data if avoidable

2) 仅在您的业务流程需要时才重复价格数据。如果可以避免，最好的方法是不重复数据

3) Depends on database used, SQL server has CHECK constraint you can use with the Len() to limit minimum size

3) 取决于使用的数据库，SQL server 有 CHECK 约束，你可以用 Len() 来限制最小大小

回答by Emacs User

1. How should I make it in such a way that a customer can order different type of food with different quantity in the orderfood and orderdrink table?

1. 我应该如何使客户可以在 orderfood 和 orderdrink 表中订购不同数量的不同类型的食物？

Make the PK compound fields such that each record in those order tables become unique. Then you can add multiple records for food and drink for the same customer/customer ID. But your current schema is incomplete. For example, there is no customer_ID in OrderDrinks.

制作 PK 复合字段，使这些订单表中的每条记录都变得唯一。然后，您可以为同一客户/客户 ID 添加多个食品和饮料记录。但是您当前的架构不完整。例如，OrderDrinks 中没有 customer_ID。

2. Should I repeat the food_price and drink_price in orderfood and orderdrink respectively?

2. 我应该分别在 orderfood 和 orderdrink 中重复food_price 和drink_price 吗？

No.

不。

3. For employee password, how should I limit the length between 6-15 characters?

3. 对于员工密码，我应该如何将长度限制在 6-15 个字符之间？

By using the appropriate authentication mechanism of the SQL server system. You did not mention which SQL you are using. If you want to manage this yourself, you should know the pitfalls. Any good SQL book can give you a good outline.

通过使用 SQL 服务器系统的适当身份验证机制。您没有提到您使用的是哪种 SQL。如果你想自己管理这个，你应该知道陷阱。任何好的 SQL 书籍都可以为您提供一个很好的大纲。