4

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.

  • You would do well to choose better column names. An order will pass through the hands of many employees. Which is recorded here? The one who submitted it, approved it, scheduled its delivery, produced the invoice or the one who made tea for those above? My experience suggest you will have several columns on both Order and Invoice tables. – Michael Green Jul 4 '15 at 13:46
  • @MichaelGreen Those are all the same. The business is small, and the same eployee will take the order, and process it, and generate the invoice. Thanks for the heads up though! – matteeyah Jul 4 '15 at 13:47
  • They may well be the same person but they have very different meaning. In data modeling your aim is to capture the semantics of your domain. Cardinality considerations come later. – Michael Green Jul 4 '15 at 13:51
6

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

  • Wow! Thanks! This cleared a lot of things up! I'm just unsure of what's the relation between the order (sales/work - it's a small business) and the payment (if any). When someone walks in, and pays for goods in cash, do I just link the payment directly to the order? – matteeyah Jul 4 '15 at 11:08
  • @RexGrammer If customer uses a single payment to pay in full, then you have a One to One relationship between Payment and SalesOrder. In a one-to-one you can collapse the two tables into a single table, putting the payment-related columns on the sales_order_ table. But then you cannot accept partial payments such as deposit ahead of time, or accept $20 in cash and the rest on credit card. Then you would make Payment table a child of SalesOrder. If one payment is accepted for multiple SalesOrder txns, then we have a Many-To-Many which means we need a third bridging table in between. – Basil Bourque Jul 4 '15 at 16:03
  • I get that, I was just lost if I should have a direct relationship between order and the payment, or should it go through an "immediate" (or "placeholder") invoice. It's clear now. – matteeyah Jul 4 '15 at 16:52
  • I'm accepting this, as the answer, but I'd also like to note that the other answer also really helped me. – matteeyah Jul 4 '15 at 16:54
  • An invoice has two purposes: [a] officially confirms that services/products were rendered, and [b] demands payment (the primary purpose). If payment is always made immediately upon receipt of services/products, then there is no need for an Invoice entity in your schema. – Basil Bourque Jul 4 '15 at 18:37
2

Price shouldn't be in your order table. A sales order hasMany line items. Price belongs to line item. As does delivery date.

An invoice is a request for payment. An order can have many (or no) invoices, and an invoice can be for many orders. It's a many to many relationship.

You don't really need invoices if you're doing pay-before-delivery (or don't do subscription services).

Check out the references here: Ready-to-Use Database models example . Silverston does order models well.

There can be several employees involved in an order: commissioned sales people, the order taker, etc. As well as several customers: the person placing the order, the person that will actually use the product/service, the party paying for the order, and the party to ship to.

For example, Grandma could call in to place an order for Little Suzy. Grandpa pays. The order is sent to Mom.

  • The retail store I'm modeling the DB for is actually very specific. It sells specialized sports gear, and currently there isn't an option to actually pay for someone (good you pointed that out, I might want to add that further down the road). An order doesn't even have to have a customer id, because someone that walks in the store, doesn't have an account (isn't registered) with us (that's mostly for regulars, and people buying from the website). The order has the total order price, and the order line has the line price (quantity x product price). Should I drop the order price? – matteeyah Jul 3 '15 at 22:36
  • ya, don't store the total. it will be v fast to calculate the order total from the lines. – Neil McGuigan Jul 3 '15 at 22:38
  • @RexGrammer that Silverston book also covers discounts etc as per your other question – Neil McGuigan Jul 3 '15 at 22:39
  • In a dumbed down version of the question. Should I leave the delivery address in the order or move it to the order lines (because an order is going to get shipped as one package). Also, the customer and employee IDs go in the order or invoice or both? And in the order lines should I store the line price (quantity x product price) or the product price? The reason I need invoices is because we allow a person to purchase the goods online and pay on delivery. --- I'm going to read some if not all of the suggested books. But this is kind of an immediate issue. – matteeyah Jul 3 '15 at 22:48
  • If I had to guess. I'd put the customerId in both, and the employee in the order. Because if they were in the retail store, someone served them, and immediately charged them for it. Meaning the employee that served them, also charged them. The invoice really is just for the pay-on-delivery website order stuff, and that means no employee serviced them. Thanks for all the feedback! – matteeyah Jul 3 '15 at 22:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.