CODEWITHSUNDEEP
djangodatabase-designdata-modeling
maplemike
maplemike

Reputation: 43

Modeling Data - Invoices and Line Items

I'm creating a web based point of sale (think cash register) solution with Django as the backend. I've always taken the 'classic' approach of modeling invoices and their line items.

InvoiceTable
  id
  date
  customer
  salesperson
  discount
  shipping
  subtotal
  tax
  grand_total
  [...]

InvoiceLineItems
  invoice_id // foreign key
  product_id
  unit_price
  qty
  item_discount
  extended_price
  [...]

After attempting to research best practices, I've found that there aren't many - at least no definitive source that's widely used.

The Kimball Group suggests: "Rather than holding onto the operational notion of a transaction header “object,” we recommend that you bring all the dimensionality of the header down to the line items."

See http://www.kimballgroup.com/2007/10/02/design-tip-95-patterns-to-avoid-when-modeling-headerline-item-transactions/ and http://www.kimballgroup.com/2001/07/01/design-tip-25-designing-dimensional-models-for-parent-child-applications/.

I'm new to development (only having used desktop database software before) - but from my understanding this makes sense as we can drill down the data any way we want for reporting purposes (though I imagine we could do the same with the first method by joining the tables).

My Questions

Upvotes: 4

Views: 4608

Answers (1)

Neil McGuigan
Neil McGuigan

Reputation: 48236

It sounds like you're confusing relational design and dimensional design.

A relational design is for facilitating transaction processing, and minimizing data anomalies and duplication. It's your operational database. A dimensional design is for facilitating analysis.

A relational design will have an invoices table and a line_items table and a dimensional design will have a company_invoices_customer fact table with a grain of invoice line item.

Since this is for POS, I assume you want a relational design first.

As for your questions:

First there are tons of good data modelling patterns for this scenario. See https://dba.stackexchange.com/questions/12991/ready-to-use-database-models-example/23831#23831

The invoice ID will need to be repeated for each row (so we can generate data like totals for the invoice). Is this an intentional feature of this way of modeling the data?

Yes

We often have invoice level data like notes, discounts, shipping charges, etc. - How do we represent these using this method?

Probably easiest/simplest to have a "notes" field on the invoice table.

For charges and discounts you should use abstraction (see Table Inheritance), and add them as Order Adjustments. See the book by Silverston in the link above.

Some discounts are product specific - so they belong on the line item anyway, others are invoice wide (think of a deal where you buy two separate products and receive a discount on the two) - we could we somehow allocate it across the line items?

The price of the item should be calculated at runtime based on it's default price, and any discounts or charges that apply in the current "scenario", example discount for government, nearby, on sale day. You could have hierarchical line items that reference each other, to keep things in order. Again, see Silverston book.

What do we do with invoice 'notes' - we have printed and/or internal notes, would we put the data in the line items and just repeat it for each line item?

If you want line item notes, add a notes column on the line items table.

That seems to go against data normalization. Put it in a related table?

If notes are nullable, and you want to be strict about normalization, then yes, add a invoice_notes table.

Upvotes: 7

Related Questions