Horton
Menu

Thread

  1. Difficulty modelling sales taxes

    Frank Millman <frank@chagford.com> — 2017-01-02T10:33:04Z

    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

  2. Re: Difficulty modelling sales taxes

    amul sul <sulamul@gmail.com> — 2017-01-02T10:42:28Z

    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

  3. Re: Difficulty modelling sales taxes

    Frank Millman <frank@chagford.com> — 2017-01-02T11:29:26Z

    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

  4. Re: Difficulty modelling sales taxes

    Melvin Davidson <melvin6925@gmail.com> — 2017-01-02T14:38:49Z

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

  5. Re: Difficulty modelling sales taxes

    Adrian Klaver <adrian.klaver@aklaver.com> — 2017-01-02T14:58:59Z

    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

  6. Re: Difficulty modelling sales taxes

    Melvin Davidson <melvin6925@gmail.com> — 2017-01-02T16:02:03Z

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








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

  7. Re: Difficulty modelling sales taxes

    David G. Johnston <david.g.johnston@gmail.com> — 2017-01-02T16:17:07Z

    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.

  8. Re: Difficulty modelling sales taxes

    David G. Johnston <david.g.johnston@gmail.com> — 2017-01-02T16:20:12Z

    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.

  9. Re: Difficulty modelling sales taxes

    Adrian Klaver <adrian.klaver@aklaver.com> — 2017-01-02T16:27:30Z

    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

  10. Re: Difficulty modelling sales taxes

    Vincent Veyron <vv.lists@wanadoo.fr> — 2017-01-02T16:31:19Z

    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