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?

Regards,
Amul

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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 DavidsonI reserve the right to fantasize. Whether or not you wish
to share my fantasy is entirely up to you. *

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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
<https://docs.djangoproject.com/en/1.10/ref/models/fields/#primary-key&gt;*

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

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.

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.

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&gt;

"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&gt;
*
*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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general