From: | "Frank Millman" <frank(at)chagford(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Difficulty modelling sales taxes |
Date: | 2017-01-02 10:33:04 |
Message-ID: | 68F2D01C0DE24011A43A5DDA3088A17C@FrankLaptop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | amul sul | 2017-01-02 10:42:28 | Re: Difficulty modelling sales taxes |
Previous Message | Chris Withers | 2017-01-01 21:45:35 | Re: default representation of null in psql |