Обсуждение: [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?

Regards,
Amul

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

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

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