Non-product invoice items

database-designerd

I am having difficulty representing additional charges in an invoice. I understand how to represent the association between an invoice and individual items in that invoice with the use of a invoice_item table, however, there are charges that I want to include to an invoice which are not orders or products. For example, I might want to include an ad-hoc licence fee or an additional administration fees or similar.

What I have so far looks like this:


invoice
============
PK uid
FK cust_id
date_created
status
...

invoice_line
============
FK invoice_id
FK order_id
...

order
============
PK uid
FK cust_id
date_created
status

order_line
============
FK order_id
FK product_id
actual_price
quantity

product
============
PK uid
name
desc

Now, the above mentioned additional charges cannot really be represented as products here, nor are they associated to any specific order. It should be on a level similar to order, in the sense that there might be a number of those additional charges connected to each invoice, there might in fact be such charges even in cases where there were no orders placed whatsoever within the invoice time period.

What is the recommended way to represent such additional charges in my invoice/invoice_item table?

Best Answer

Typically you would not restrict your invoice_line table to specifically products, instead create an exclusive subtype depending on what type each line is (product, fee, discount, tax, memo, etc.).

CREATE TABLE line_type
(
  line_type_cd  CHAR(1)      NOT NULL
 ,description   VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_line_type PRIMARY KEY (invoice_line_type_cd)
 ,CONSTRAINT AK_line_type UNIQUE (invoice_line_type_cd)
)
;

CREATE TABLE invoice_line
(
  invoice_id    INT           NOT NULL
 ,line_nbr      SMALLINT      NOT NULL
 ,line_type_cd  CHAR(1)       NOT NULL
 ,quantity      DECIMAL(9,2)  NOT NULL
 ,amount        DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_invoice_line_for_invoice FOREIGN KEY (invoice_id) REFERENCES invoice (invoice_id)
 ,CONSTRAINT FK_invoice_line_discriminated_by_line_type FOREIGN KEY (line_type_cd) REFERENCES line_type (line_type_cd)
 ,CONSTRAINT PK_invoice_line PRIMARY KEY (invoice_id, line_nbr)
)
;

CREATE TABLE invoice_product
(
  invoice_id        INT       NOT NULL
 ,product_line_nbr  SMALLINT  NOT NULL
 ,product_id        INT       NOT NULL
 ,order_id          UUID      NOT NULL
 ,CONSTRAINT FK_invoice_line_fulfills_order_line FOREIGN KEY (order_id, product_id) REFERENCES order_line (order_id, product_id)
 ,CONSTRAINT FK_invoice_product_is_invoice_line FOREIGN KEY (invoice_id, product_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, product_line_nbr)
)
;

CREATE TABLE invoice_fee
(
  invoice_id    INT       NOT NULL
 ,fee_line_nbr  SMALLINT  NOT NULL
 ,fee_cd        CHAR(6)   NOT NULL
 ,CONSTRAINT FK_invoice_line_charges_fee FOREIGN KEY (fee_cd) REFERENCES fee (fee_cd)
 ,CONSTRAINT FK_invoice_fee_is_invoice_line FOREIGN KEY (invoice_id, fee_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, fee_line_nbr)
)
;

CREATE TABLE invoice_tax
(
  invoice_id    INT       NOT NULL
 ,tax_line_nbr  SMALLINT  NOT NULL
 ,tax_auth_cd   CHAR(6)   NOT NULL
 ,CONSTRAINT FK_invoice_line_remitted_to_tax_authority FOREIGN KEY (tax_authority_cd) REFERENCES tax_authority (tax_authority_cd)
 ,CONSTRAINT FK_invoice_tax_is_invoice_line FOREIGN KEY (invoice_id, tax_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, tax_line_nbr)
)
;

The last step is to add a function/trigger to ensure the insert into each of the subtype tables corresponds to the appropriate line_type.

An invoice_product table isn't something to be avoided - you need a way to separate concerns/unique attributes while still associating the quantities/amounts to each invoice_line.

A few other observations:

There is a normalization error that will permit invalid order/customer combinations for an invoice. So you'll need to either:

  1. Restrict an invoice to one order - thus allowing one path to customer (inflexible).
  2. Make customer_id part of the primary key of invoice and order and use the power of the composite key to enforce the relationship (most flexible).

The second option would look like this:

CREATE TABLE invoice
(
  customer_id  INT   NOT NULL
 ,invoice_id   INT   NOT NULL
 /* everything else */
 ,CONSTRAINT FK_invoice_for_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id)
 ,CONSTRAINT PK_invoice PRIMARY KEY (invoice_id, customer_id)
 ,CONSTRAINT AK_invoice UNIQUE (invoice_id)
)
;

CREATE TABLE order
(
  cust_id         INT          NOT NULL
 ,order_nbr       INT          NOT NULL
 ,cust_order_ref  VARCHAR(20)  NOT NULL
 /* everything else */
 ,CONSTRAINT FK_order_placed_by_customer FOREIGN KEY (cust_id) REFERENCES customer (cust_id)
 ,CONSTRAINT PK_order PRIMARY KEY (cust_id, order_nbr)
 ,CONSTRAINT AK_order UNIQUE (cust_id, cust_order_ref)
)
;

At this point cust_id would migrate to your invoice_line table (and subtypes) and enforce only items will be billed to the customer that were placed by the customer This also allows you to avoid the ugly UUID data type - much easier to reference a smaller composite key than break everything with a 16-byte row pointer:

CREATE TABLE invoice_line
(
  invoice_id    INT           NOT NULL
 ,cust_id       INT           NOT NULL
 ,line_nbr      SMALLINT      NOT NULL
 ,line_type_cd  CHAR(1)       NOT NULL
 ,quantity      DECIMAL(9,2)  NOT NULL
 ,amount        DECIMAL(9,2)  NOT NULL
 ,CONSTRAINT FK_invoice_line_for_invoice FOREIGN KEY (invoice_id, cust_id) REFERENCES invoice (invoice_id, cust_id)
 ,CONSTRAINT FK_invoice_line_discriminated_by_line_type FOREIGN KEY (line_type_cd) REFERENCES line_type (line_type_cd)
 ,CONSTRAINT PK_invoice_line PRIMARY KEY (invoice_id, cust_id, line_nbr)
)
;

CREATE TABLE invoice_product
(
  invoice_id        INT       NOT NULL
 ,cust_id           INT       NOT NULL
 ,product_line_nbr  SMALLINT  NOT NULL
 ,product_id        INT       NOT NULL
 ,order_nbr         INT       NOT NULL
 ,CONSTRAINT FK_invoice_line_fulfills_order_line FOREIGN KEY (cust_id, order_nbr, product_id) REFERENCES order_line (cust_id, order_nbr, product_id)
 ,CONSTRAINT FK_invoice_product_is_invoice_line FOREIGN KEY (invoice_id, cust_id, product_line_nbr) REFERENCES invoice_line (invoice_id, line_nbr)
 ,CONSTRAINT PK_invoice_fee PRIMARY KEY (invoice_id, cust_id, product_line_nbr)
)
;

The appropriate choice will be dependent on your other requirements - if customers do not want multiple orders combined on a single invoice, the first option is perfectly acceptable and there are other ways to handle if multiple orders were fulfilled at the same time (you'd generally have a manifest or packing_list entity that could handle this).

Related Question