Re: [GENERAL] Difficulty modelling sales taxes

On Mon, 2 Jan 2017 12:33:04 +0200
"Frank Millman" <frank@chagford.com> wrote:

>
> I want to model sales taxes in a flexible manner. I need one table to define tax categories (e.g. VAT) and a
subsidiarytable to define tax codes within each category (e.g. Standard Rate). 
>
> CREATE TABLE tax_categories (
>     row_id SERIAL PRIMARY KEY,
>     category text NOT NULL,
>     description text NOT NULL,
>     CONSTRAINT _tax_cats UNIQUE (category));
>

As Melvin wrote, unless you have a reason not to do so, use a natural key when possible.

CREATE TABLE tax_categories (
    category text PRIMARY KEY,
    description text NOT NULL);


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

Here I would use a surrogate PK, which will be used in prod_tax_codes; there is no apparent tax rate in your schema, so
Iused 'code' for that. 'description' seems superfluous. 

CREATE TABLE tax_codes (
     tax_code_id serial primary key,
     category text NOT NULL REFERENCES tax_categories,
     code numeric NOT NULL,
     CONSTRAINT _tax_codes UNIQUE (category, code));


>
> Now I want to assign tax codes to product codes. As each product could potentially have more than one tax code, I
needa 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
taxcode 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 text 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));
>

create table prod_codes (product_id serial primary key, libelle text);

CREATE TABLE prod_tax_codes (
     product_id INT NOT NULL REFERENCES prod_codes,
     tax_code_id INT NOT NULL REFERENCES tax_codes,
     CONSTRAINT _prod_tax_code_1 UNIQUE (product_id, tax_code_id));

Just a few lines less, but I find it pays in the long run for development/maintenance purposes.


--
                    Bien à vous, Vincent Veyron

https://marica.fr/
Gestion des sinistres assurances, des dossiers contentieux et des contrats pour le service juridique