how to design the tables for customer orders and bills

Question

Am designing a database schema (orders and bills) for a hotel system. The attached image shows the tables in the database schema.

The question is how do I design the bills table, so that I can calculate the customer bill from orders the customer has made?

My assumption is that a bill is calculated after the order is made, and not the other way round, e.g. creating a bill before we make an order.

I am considering this answer however it does not solve my problem, since I want to calculate bills from customer orders.

The red rectangle shows the relationship between the orders and bill table this is where am stuck, I don't know how to design the tables.

Answer 1

EDIT 3 added design + cleanup

Every base table is the rows satisfying some statement. Find the statement.

Customer is the rows satisfying: customer [CustomerId] named [CustomerName] lives at ...
Product is the rows satisfying: product [ProductId] is named [Productname] costing [ProductPrice]
OrderDetail is the rows satisfying: orderDetail [OrderDetailId] of order [OrderId] is quantity [quantity] of product [ProductId]
Order is the rows satisfying: customer [CustomerId] ordered [OrderId] on [dateOfOrder]

What rows do you want in Bill? I'll guess...

Bill is the rows satisfying:
    Bill [BillId] is for order [OrderId] on [dateOfBill]  ... ???

You can find out some things about a bill by using its order. You must determine what else besides its date and order that you want know about a bill (eg to write one) and then what statement bill rows satisfy (ie finish the "...") that gives you that info directly (as with its date) or indirectly (as with its order).

I asked

what else besides its date and order that you want know about a bill (eg to write one)

BillId
dateOfBill
OrderId
order OrderId's customer's CustomerId, CustomerName, CustomerAddress ...
order OrderId's dateOfOrder
for every orderDetailId's orderDetail whose orderID = OrderId
    quantity, ProductId, ProductNam,e ProductPrice, (quantity * ProductPrice) as productProduct
sum(quantity * ProductPrice) as total
    over every orderDetail with OrderDetailId = OrderId

I asked

what statement bill rows satisfy that gives you that info directly or indirectly

You suggested

For the bills table I intend to have the following fields Bill BillId (PK) CustomerId (FK) OrderId (FK) dateOfBill

Bill has to directly give us a BillId, dateOfBill and OrderId; they're nowhere else. But everything else can be got indirectly.

Bill is the rows satisfying:
    bill [BillId] is for order [OrderId] and was billed on [dateOfBill]

The reason I mention statements is: one needs them to query and to determine FDs, keys, uniqueness, Fks, and other constraints. (Rather than using one vague intuitions.) This is explicit in design methods ORM2, NIAM and FCO-IM.

I determined the content of a bill above by finding what statement its rows will satisfy:

customer [CustomerId] named [CustomerName] at [CustomerAddress] ...
owes us $[total] for order [OrderId]
ordering [quantity] of product [ProductId] named [ProductName] @ price $[ProductPrice] = [productProduct] 
as recorded in bill [BillId]

This is a statement made from the statements given for each table, except that I need some statements not in any table yet, namely the stuff that (therefore) Bill needs to give. By replacing the statements by their tables we will get the query whose values are the rows we want.