Invoicing vs Quoting or Estimating

Question

If invoices can be voided, should they be used as quotations?

I have an Invoices tables that is created from inventory associated with a Job or Order. I could have a Quotes table as a halfway-house between inventory and invoices, but it feels like I would have duplicate data structures and logic just to handle an "Is this a quote?" bit.

From a business perspective, quotes are different from invoices: a quote is sent prior to an undertaking and an invoice is sent once it is complete and payment is due, but how to represent this in my repository and model.

• What is an elegant way to store and manage quotes & invoices in a database?

Edit: indicated Job === Order for this particular instance.

Answer 1

There are 3 approaches:

1. Store invoices and quotes in separate tables.

   This is a good design if invoices and quotes have few fields in duplicate (otherwise, use option #3 with 3 tables), and if there's a 1-many or many-many relationships between them (for 1-1, use option #2).

   This is also a good choice if it's common that "shared" information between the two can actually mutate when the quote becomes the invoice (although some of these mutations should be properly handled with separate fields/tables, such as applied discounts, etc...).

   A slight variation of this option is obviously needed be done when multiple quotes are turned into a single (or multiple) invoices. This adds a 3rd table which is a mapping between a set of quotes and an invoice (or set of invoices if it gets that complicated) for them.

2. Store them in the same table, with extra flag "Invoice or quote" and any extra fields from both stored. This can be done with either invoices and quotes in distinct rows, or with them sharing rows (with flag having "both" value too).

   The latter (same row can be both invoice and quote) is a good choice if they are mapped 1 to 1, and there are few fields that distinguish the two.

   The former (separate rows for invoices and quotes) is not a vary good design in general and better done with the #3 or #1 options.

3. Have 3 tables, one for common fields between the two, and two for invoice-only and quotes only.

   This is a good choice if invoices and quotes are mapped 1-1, or if they are 1-many but each of the many invoices has exactly the same field values for whichever fields are common. Otherwise, use #1.

   A slight variation of this option can be done when multiple quotes are turned into a single invoice. This adds a 4th table which is a mapping between a set of quotes and an incoice (or set of invoices if it gets that complicated) for them. Again, the assumption here is that there's a sizeable chunk of common info between all of the quotes and invoices linked/combined together, otherwise just go with #1.

Answer 2

Quotes are more analogous to orders. I have seen several distribution/retail systems with an order table that has a boolean flag named something like IsQuote. This can seem simple as it makes it trivial to turn a quote into an order. I never liked it because orders that come out of quotes are not always exactly as quoted. As a result, systems like those lose information that can be of some use (i.e. a report that compares quotes to orders). Therefore, I prefer systems where the quote and order tables are about the same but separate. In distribution systems this often leads to tables like OrderHeader, OrderLine (relates to item/inventory table), QuoteHeader and QuoteLine. You might also have a table to model a relationship where one quote can map to multiple orders.

Invoices usually result from orders. Sometimes more than one order will be billed on a single invoice. For example, there are cases where I have seen companies bill monthly to their good customers. I have also seen it work the other way where a large order with multiple shipments is billed on multiple invoices (one for each shipment).

Finally, payments usually have a many to many relationship with the invoice. Sometimes one payment covers multiple invoices. Sometimes one invoice gets paid in a couple of payments.

Answer 3

I would recommend being as flexible as possible. Use the following tables

Job Table, Invoice Table, Quote Table

In your Invoice Table and Quote Table, store the Job Id, give it an index and create a foreign key constraint. Leave the clustered index on the quote id and the invoice id.

Answer 4

On the last system I worked on the only difference between quotes and invoices (in terms of the db) was a flag on the table that indicated whether the quote had been accepted by the customer (at which point another statement was generated with all the same information except that it was an invoice instead of a quote)