Re: insert

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