Thread: [GENERAL] Difficulty modelling sales taxes

[GENERAL] Difficulty modelling sales taxes

From

"Frank Millman"

Date:

02 January 2017, 16:33:04

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

Re: [GENERAL] Difficulty modelling sales taxes

From

amul sul

Date:

02 January 2017, 16:42:28

On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <frank@chagford.com> wrote:
> Hi all
>
> 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.
>
NVARCHAR ?  Are you using PostgreSQL as database server?

Regards,
Amul

Re: [GENERAL] Difficulty modelling sales taxes

From

"Frank Millman"

Date:

02 January 2017, 17:29:26

From: amul sul

Sent: Monday, January 02, 2017 12:42 PM

To: Frank Millman

Cc: pgsql-general

Subject: Re: [GENERAL] Difficulty modelling sales taxes

> On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <frank@chagford.com> wrote:

> Hi all

> 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.

NVARCHAR ? Are you using PostgreSQL as database server?

Oops, sorry.

I am testing with PostgreSQL and with SQL Server, so I was in the wrong mindset when I posted.

I should have said VARCHAR.

Frank

Re: [GENERAL] Difficulty modelling sales taxes

From

Melvin Davidson

Date:

02 January 2017, 20:38:49

On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman <frank@chagford.com> wrote:

From: amul sul
Sent: Monday, January 02, 2017 12:42 PM
To: Frank Millman
Cc: pgsql-general
Subject: Re: [GENERAL] Difficulty modelling sales taxes

> On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <frank@chagford.com> wrote:
>
> Hi all
>
>
>
> 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.
>
>
>
NVARCHAR ? Are you using PostgreSQL as database server?
>

Oops, sorry.

I am testing with PostgreSQL and with SQL Server, so I was in the wrong mindset when I posted.

I should have said VARCHAR.

Frank

First, there is no need to make row_id's when you already have a valid primary key.
Next, DO NOT begin object names with underscores.
So try this model instead:

CREATE TABLE tax_categories (
    tax_category VARCHAR() NOT NULL,
    description VARCHAR() NOT NULL,
    CONSTRAINT tax_cats_pk PRIMARY KEY (tax_category)
    );

CREATE TABLE tax_codes (
    tax_category VARCHAR() NOT NULL,
    code VARCHAR() NOT NULL,
    description VARCHAR() NOT NULL,
    CONSTRAINT tax_codes_pk PRIMARY KEY (tax_category, code),
    CONSTRAINT tax_category_fk (tax_category)
    FOREIGN KEY REFERENCES tax_categories (tax_category)
    );

CREATE INDEX idx_tax_category
ON tax_codes
USING BTREE (tax_category);

CREATE INDEX idx_code
ON tax_codes
USING BTREE (code);

--

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Difficulty modelling sales taxes

From

Adrian Klaver

Date:

02 January 2017, 20:58:59

On 01/02/2017 06:38 AM, Melvin Davidson wrote:
>
> On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman <frank@chagford.com
> <mailto:frank@chagford.com>> wrote:
>
>
>     *From:* amul sul
>     *Sent:* Monday, January 02, 2017 12:42 PM
>     *To:* Frank Millman
>     *Cc:* pgsql-general
>     *Subject:* Re: [GENERAL] Difficulty modelling sales taxes
>
>     > On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <frank@chagford.com
>     <mailto:frank@chagford.com>> wrote:
>     >
>     > Hi all
>     >
>     >
>     >
>     > 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.
>     >
>     >
>     >
>     NVARCHAR ?  Are you using PostgreSQL as database server?
>     >
>
>
>     Oops, sorry.
>
>     I am testing with PostgreSQL and with SQL Server, so I was in the
>     wrong mindset when I posted.
>
>     I should have said VARCHAR.
>
>     Frank
>
>
>
>
>
> *First, there is no need to make row_id's when you already have a valid
> primary key.

In a perfect world yes, but this is a world with ORM's as I found out
the hard way:

https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key

"The primary key field is read-only. If you change the value of the
primary key on an existing object and then save it, a new object will be
created alongside the old one."



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] Difficulty modelling sales taxes

From

Melvin Davidson

Date:

02 January 2017, 22:02:03

On Mon, Jan 2, 2017 at 9:58 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 01/02/2017 06:38 AM, Melvin Davidson wrote:

On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman <frank@chagford.com
<mailto:frank@chagford.com>> wrote:

*From:* amul sul
*Sent:* Monday, January 02, 2017 12:42 PM
*To:* Frank Millman
*Cc:* pgsql-general
*Subject:* Re: [GENERAL] Difficulty modelling sales taxes

> On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <frank@chagford.com
<mailto:frank@chagford.com>> wrote:
>
> Hi all
>
>
>
> 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.
>
>
>
NVARCHAR ? Are you using PostgreSQL as database server?
>

Oops, sorry.

I am testing with PostgreSQL and with SQL Server, so I was in the
wrong mindset when I posted.

I should have said VARCHAR.

Frank

*First, there is no need to make row_id's when you already have a valid
primary key.

In a perfect world yes, but this is a world with ORM's as I found out the hard way:

https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key

"The primary key field is read-only. If you change the value of the primary key on an existing object and then save it, a new object will be created alongside the old one."

--
Adrian Klaver
adrian.klaver@aklaver.com

>https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key

You are quoting from a django document. Please don't muddle the waters.
So you are saying this will not work?
UPDATE mytable
SET mykey = 'new_value'
WHERE mykey = 'old_value';

DELETE FROM mytable
WHERE mykey = 'old_value';

Happy New Year Adrian

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Difficulty modelling sales taxes

From

"David G. Johnston"

Date:

02 January 2017, 22:17:07

On Mon, Jan 2, 2017 at 3:33 AM, Frank Millman <frank@chagford.com> wrote:

CONSTRAINT _prod_tax_code_2 FOREIGN KEY (category_id, tax_code) REFERENCES tax_codes (category_id, code));

This is typical solution to this modelling problem. If you need to do other stuff (introduce redundancies) to make it play nice with the ORM then that is valid if unfortunate.

David J.

Re: [GENERAL] Difficulty modelling sales taxes

From

"David G. Johnston"

Date:

02 January 2017, 22:20:12

On Mon, Jan 2, 2017 at 7:38 AM, Melvin Davidson <melvin6925@gmail.com> wrote:

Next, DO NOT begin object names with underscores.

This is only a bit arbitrary (though I do subscribe to it)...naming custom data types or tables with underscores is likely to introduce problems since an implementation detail of PostgreSQL is that the array version of a given type is named "_{type}" (e.g. _varchar).

David J.

Re: [GENERAL] Difficulty modelling sales taxes

From

Adrian Klaver

Date:

02 January 2017, 22:27:30

On 01/02/2017 08:02 AM, Melvin Davidson wrote:
>
>

>
>         *First, there is no need to make row_id's when you already have
>         a valid
>         primary key.
>
>
>     In a perfect world yes, but this is a world with ORM's as I found
>     out the hard way:
>
>     https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key
<https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key>
>
>     "The primary key field is read-only. If you change the value of the
>     primary key on an existing object and then save it, a new object
>     will be created alongside the old one."
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>
> *>https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key
> <https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key>
> *
> *You are quoting from a django document.  Please don't muddle the waters.

Not sure I follow. You said:

"First, there is no need to make row_id's ..."

I was just pointing out that this is not a hard and fast rule and that
sometimes you are required to come up with a compromise solution.

> So you are saying this will not work?

Yes, if you want to bypass the ORM and want to run into FK problems.

> UPDATE mytable
>   SET mykey = 'new_value'
>   WHERE mykey = 'old_value';

Under Django you would now have one row with the new_value and another
with the old_value. Assuming mytable has child tables that reference
mykey, the child tables would be still referencing the old_value.

>
> DELETE FROM mytable
>   WHERE mykey = 'old_value';

What happens now depends on how you have CASCADING set up. In any case
you would be doing more work to bring the child tables back into sync
with the parent table. For me the easiest the thing to do was bow to the
ORM convention and put in an integer PK.

> *
> *Happy New Year Adrian*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.

--
Adrian Klaver
adrian.klaver@aklaver.com

Re: [GENERAL] Difficulty modelling sales taxes

From

Vincent Veyron

Date:

02 January 2017, 22:31:19

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