Message-ID: <4E339585.9080301@archidevsys.co.nz>
Date: Sat, 30 Jul 2011 17:24:21 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110621 Fedora/3.1.11-1.fc14
	Lightning/1.0b3pre Thunderbird/3.1.11
MIME-Version: 1.0
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
CC: alan <alan.miller3@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: insert
References: 
 <2e208bc6-1bb8-4170-809f-77ab63a15bf4@l18g2000yql.googlegroups.com>
	<4E32CE5D020000250003F8FF@gw.wicourts.gov>
In-Reply-To: <4E32CE5D020000250003F8FF@gw.wicourts.gov>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 30/07/11 08:14, Kevin Grittner wrote:
> alan<alan.miller3@gmail.com>  wrote:
>
>> Can I write a BEFORE ROW trigger for the products table  to runs
>> on INSERT or UPDATE to
>>   1. insert a new category&  return the new category_id  OR
>>   2.  return the existing category_id for the (to be inserted row)
>
> What would you be using to match an existing category?  If this
> accurately identifies a category, why not use it for the key to the
> category table, rather than generating a synthetic key value?
>
> -Kevin
>
Hi Alan,

This is the way I would define the tables, I think it conforms tom your 
requirements, and the definitions look clearer.

I have the convention that the id of the table itself is not prefixed 
with the table name, but references to the id field of other tables are 
(e.g. category_id). This is not something you need to follow, but it 
helps to clearly identify what is a foreign key, and what is the current 
table's id!  Likewise, I think it is simpler to make the table names 
singular, but this again is a bit arbitrary.

I guess, even if you prefer my conventions, it is more important to 
follow the standards of the existing database!


CREATE TABLE product
(
     id          SERIAL PRIMARY KEY,
     category_id int REFERENCES category(id),
     name        VARCHAR(60) NOT NULL
);

CREATE TABLE category
(
     id      SERIAL PRIMARY KEY,
     name    VARCHAR(20) UNIQUE NOT NULL
);

Though for the primary key of the category table, it might be better to 
explicitly assign the key, then you have more control of the numbers used.

I would be a bit wary of automatically inserting a new category, when 
the given category is not already there, you could end up with several 
variations of spelling for the same category!  I once saw a system with 
about 20 variations of spelling, and number of spaces between words, for 
the name of the same company!

Possibly your client GUI application could have a drop down list of 
available categories, and provision to enter new ones, but then this 
might be outside your control.


Cheers,
GAvin