Modeling invoices and orders

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.

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).

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.

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.