On Mon, Jan 2, 2017 at 6:29 AM, Frank Millman <fr...@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 <fr...@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. *
