[GENERAL] Difficulty modelling sales taxes

[Frank Millman]

Hi all

I want to model sales taxes in a flexible manner. I need one table to define 
tax categories (e.g. VAT) and a subsidiary table to define tax codes within 
each category (e.g. Standard Rate).

CREATE TABLE tax_categories (
    row_id SERIAL PRIMARY KEY,
    category NVARCHAR NOT NULL,
    description NVARCHAR NOT NULL,
    CONSTRAINT _tax_cats UNIQUE (category));

CREATE TABLE tax_codes (
    row_id SERIAL PRIMARY KEY,
    category_id INT NOT NULL REFERENCES tax_categories,
    code NVARCHAR NOT NULL,
    description NVARCHAR NOT NULL,
    CONSTRAINT _tax_codes UNIQUE (category_id, code));

Now I want to assign tax codes to product codes. As each product could 
potentially have more than one tax code, I need a many-to-many table.

My difficulty is that each product can have tax codes from different tax 
categories, but it cannot have more than one tax code from the same tax 
category. I am not sure how to model this ‘uniqueness’.

The best I can come up with is this -

CREATE TABLE prod_tax_codes (
    product_id INT NOT NULL REFERENCES prod_codes,
    category_id INT NOT NULL REFERENCES tax_categories,
    tax_code NVARCHAR NOT NULL,
    CONSTRAINT _prod_tax_code_1 UNIQUE (product_id, category_id),
    CONSTRAINT _prod_tax_code_2 FOREIGN KEY (category_id, tax_code) REFERENCES 
tax_codes (category_id, code));

It is a bit ugly, because I have to use the ‘NVARCHAR code’ column from 
tax_codes, not the primary key, but I think it would work.

Does anyone have any better ideas?

Thanks

Frank Millman