How can I design a database for ordering food as a waiter

I have these tables:

 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 have the details when a user is ordering food, with each row representing one of the items.

Per example:

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

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)

Solution

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;