我如何设计一个数据库来作为服务员点菜 [英] How can I design a database for ordering food as a waiter

问题描述

我有这些表：

 User (Username PK, password)
    Order (OrderId PK,date,nettotal,userid FK_User_Order)
    FoodItem(FoodID PK,Price)
    OrderDetails(DetailId PK,OrderId FK_Order_OrderDetails,FoodID FK_Food_OrderDetails,quantity,totalsum)

OrderDetails具有用户订购食物时的详细信息，每一行代表一项。

OrderDetails have the details when a user is ordering food, with each row representing one of the items.

每个示例：

如果用户订购了2个披萨（Food Id = 1）和1个汉堡（FoodId = 2）
，我希望我的OrderDetails像这样填充2行：

If a user ordered 2 pizzas (Food Id=1) and 1 burger (FoodId=2) I would expect my OrderDetails to be filled with 2 rows like this:

1st row: DetailID=1,OrderId=1,FoodId=1,quantity=2,price=500
2nd row: DetailID=1,OrderId=1,FoodId=2,quantity=1,price=250

我需要它们具有相同的OrderId，这样我才能知道它们属于一个订单，但是如果是第一次，那么Order表中没有行。我该如何设计，以便他们创建这样的订单行：

I need them to have the same OrderId so I can know they belong to one order, but if it is the first time there is no rows in the Order table. How can I design this so they create an order row like this:

OrderId=1,date=SYSDATE,nettotal=500+250,userid=1)

推荐答案

这是伪SQL，但您使用的是什么执行 INSERT 时，需要的是 OUTPUT 子句。如果我们假设，您具有订单本身的标量参数和食品项的表类型参数，那么您的SQL看起来将像这样：

This is psuedo SQL here, but what you need is the OUTPUT clause when you perform the INSERT. If we assume you have scalar parameters for the order itself and a table type parameter for the food items, then you're SQL is going to look a little like this:

DECLARE @Order table (OrderID int)

INSERT INTO dbo.OrderTable (OrderDate, UserID) --NetTotal shouldn't really be stroed, as it can be derived from the order details
OUTPUT inserted.OrderID
INTO @Order (OrderID)
SELECT GETDATE(), @UserID;

INSERT INTO dbo.OrderItems (ItemID, OrderID, Quantity)
SELECT I.ItemID,
       O.OrderID,
       I.Quantity
FROM @Items I --YOur table type parameter
     CROSS JOIN @Order O;

这篇关于我如何设计一个数据库来作为服务员点菜的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！