From: | Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> |
---|---|
To: | "Frank Millman" <frank(at)chagford(dot)com> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Difficulty modelling sales taxes |
Date: | 2017-01-02 16:31:19 |
Message-ID: | 20170102173119.1dbfb8edb92754800218f775@wanadoo.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, 2 Jan 2017 12:33:04 +0200
"Frank Millman" <frank(at)chagford(dot)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 subsidiary table 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 I used '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 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 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
From | Date | Subject | |
---|---|---|---|
Next Message | vod vos | 2017-01-02 17:03:07 | Re: COPY: row is too big |
Previous Message | Adrian Klaver | 2017-01-02 16:27:30 | Re: Difficulty modelling sales taxes |