MySQL – Modeling Invoices and Orders

database-designMySQLnormalizationtransaction

I have a problem in modeling a database that contains orders and invoices. Actually it's more of a problem from a business point of view, but it's related to modeling the database nonetheless.

Model of the Orders table:

Model of the Orders table

Now I had another column called payment_deadline, that well had the payment deadline. What I'm thinking about is, what info should my Orders table have and what info should my Invoices table have?

Which one has Employee and Customer id? Which one has the prices? Which one has the payment info? Which one has the deadline info? I'm totally lost.

I understand that in the business world, an order signals that the provider of the goods has to act, and the invoice represents that the goods provider has fulfilled his part and the customer now has to pay for the goods/services. But I'm lost as to how model it in the database. Any insight is much appreciated.

Best Answer

Your Question is much too broad to provide any specific table design suggestions. Just this, "Which one has Employee", could mean any of dozens of things as mentioned in the Answer by Neil McGuigan: the person who first took customer inquiry phone call, the sales person who responded, the sales assistant who filled out Sales Order, the production manager who acknowledged receipt of the Sales Order, the workers who produced the items on the work order, and so on.

But I'll give you a simplified overview that might provide some of the insight you requested.

Business Transaction Life-Cycle

Using the definitions from Wikipedia, the usual work flow of vendor-customer is shown in this diagram. Colors mean:

  • Pink items = customer-vendor contact/interaction without documents
  • Blue items = documents from/to the customer
  • Yellow items = documents internal to the vendor.

enter image description here

After consulting with a sales person (optional), the customer submits a Purchase Order to a vendor, describing exactly what service/product they have decided to buy. If accepted by the vendor, a legally-binding contract is established.

The Sales Order is produced by the sales people of the vendor, reflecting the content of the Purchase Order but using the format and lingo that makes sense to the internal staff of the vendor.

The vendor's internal staff, the production-related managers (senior artisans, manufacturing managers, warehouse supervisors, etc.) create one or more Work Order documents directing their staff on what to do to render the services/products desired by the customer.

When any part or whole of the Purchase Item has been rendered to the customer, an Invoice is prepared by the vendor and delivered to the customer to demand payment. The Invoice should reference the Purchase Order number/id.

Lastly, the customer makes payment along with some kind of Remittance Advice referencing the Invoice number/id. This step completes the transaction (ignoring returns, Change Orders, or other issues).

The diagram above is not a table or entity diagram. For example, you may well have multiple work orders for one sales order. You may have multiple invoices if partial deliveries are made to the customer. Or, imagine if the manufacturing staff only builds two units of WonderWidget at a time to avoid wastage. So we must wait for another customer to order a second widget, while waiting we do not yet produce a Work Order. Meanwhile we can start on other items on the sales order, and we produce Work Orders for these early items. In this scenario we have a Many-to-Many relationship where any Sales Order can have more than one Work Order (customer wants several items with some of those items being produced while waiting for WonderWidget production to begin) while each Work Order can have more than one Sales Order attached (a pair of Sales Orders from different customers in case of double-WonderWidget production).

In-Person Retail

In-person retail is much simpler, with an work flow of three instantaneous steps, only two of which would be documents (or database entities).

enter image description here

A customer grabs some stuff from the store shelf, presents at the checkout counter, a register tallies up the items (akin to a sales order), payment is executed, and a sales receipt is produced. The Sales Receipt is basically the combination of the register's tally (sales order) plus the payment details (partial credit card number, date-time, and so on).

Again, this is simplified because we ignore returns and such issues.

Again this diagram is not a table or entity diagram.

Web/Phone Retail

With online web or telephone ordering, the work flow is probably a combination of the two above. If the customer pays immediately, then we don't need the Invoice & Remittance Advice. If it's a small shop, the Sales Order and Work Order may be the same single entity, whereas if this is an Amazon.com style business with multiple warehouses, then we will have multiple work orders.

Over-Simplified Partial Table Diagram

Just to give you a flavor as a newbie to table design, here is a grossly over-simplified design. Partial design, just for the sales order. This diagram uses simple crows-foot notation and Postgres data types. Abbreviations: pk means primary key, and fk means foreign key.

over-simplified table design diagram

Related Question