Re: Difficulty modelling sales taxes

[Melvin Davidson <melvin6925@xxxxxxxxx>]

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

 

From: amul sul

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

To: Frank Millman

Cc: pgsql-general

Subject: Re: Difficulty modelling sales taxes

 

> On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman <frank@xxxxxxxxxxxx> 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.