Re: insert

On 30/07/11 08:14, Kevin Grittner wrote:
> alan<alan(dot)miller3(at)gmail(dot)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