E
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:
Restrict an invoice to one order - thus allowing one path to customer (inflexible).
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).
