how to design the tables for customer orders and bills

Question

enter image description here

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.

A Bill can be linked to one or more Orders. You would create the Bill as soon as the first Order is created. You don't complete filling in the columns of the Bill until it's time for the customer to pay. A lot of Hotel systems today calculate the bill as soon as the Customer checks in, and expect payment for the room nights. The Customer's credit card is charged for any additional charges on a daily basis until he checks out. — Gilbert Le Blanc, Jun 03 '14 at 21:55
@GilbertLeBlanc suppose we say that an order has a bill, meaning that a bill is linked to a single order. and introduce another table to get sum of individual bills. I was trying to avoid creating the bill at the time of making the order. I want to use an approach where the bill is calculated after orders are made. — patiwagura, Jun 03 '14 at 22:15

score 2 · Answer 1 · answered Jun 09 '14 at 17:48

Some language standardization first:

By Order you mean Sales Order, OrderDetails are called Line Items, and a Bill is usually called a Sales Invoice.

An sales invoice is a request for payment. You issue one when you think someone owes you money.

Depending on the terms of the sales order, someone owes you money:

after the order is completed
after the service is first delivered
after the service is completely delivered
after some period of time based on the terms of the sales order

For a hotel, usually you ask for money after the service has been completely delivered, but perhaps with a deposit, or intermediate payments for a long stay.

An invoice is not necessarily for one sales order. You can combine multiple sales orders into one invoice.

An invoice has line items referencing the sales order line items that you are requesting payment for.

You may have to issue multiple invoices for the same person/sales order.

I appreciate your contribution, I had included a diagram which was self explanatory, I wasn't much concerned on how to name the tables but the relationship between the Sales Order(orders) and sales Invoice (bill). I have also noted that the design requires that Bills( Sales Invoice) exist before we create the Sales Order. I would appreciate if someone suggested any other db-design where the bill would be computed after sales order is completed. — patiwagura, Jun 14 '14 at 09:36

philipxy · Accepted Answer · 2014-06-04T23:53:22.693

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.

@phillip I didn't understand what you meant, could you please elaborate by using table schemas or diagrams. For the bills table i intend to have the following fieldsbillid (Pk) customerid (Fk) orderid (fk) dateofbill — patiwagura, Jun 04 '14 at 14:31
@glapo 1. What is the first thing you don't understand? 2.Suppose I see a row of values (f,c,o,d) in Bill. I'm willing to guess what that row tells me. Does it say that customer [customerId] went to the circus on [dateOfBill] and juggled the green parts detailed in order [orderId]? No? Ok then: 2. Tell me what the row tells me. 3. Then tell me what it tells me when it's not in Bill. 4. Tell me how you expect someone to put a row into Bill without knowing 2 & 3. [This might help.](http://stackoverflow.com/a/23842061) — philipxy, Jun 04 '14 at 15:17
i have to admit that it has taken me days to understand what you meant, Your contribution has solved my problem. do you have Db-design tutorials I can read. I would also suggest that you use simple grammar in your comments. thanks alot — patiwagura, Jun 18 '14 at 08:43

how to design the tables for customer orders and bills

2 Answers2