Thread: insert

next question.

I have a product table with a 'category" column that I want to
maintain in a separate table.

CREATE TABLE products (
                product_id INTEGER DEFAULT
nextval('product_id_seq'::regclass) NOT NULL,
                name VARCHAR(60) NOT NULL,
                category SMALLINT  NOT NULL,
                CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE TABLE products (
                category_id INTEGER DEFAULT
nextval('category_id_seq'::regclass) NOT NULL,
                name VARCHAR(20) NOT NULL,
                CONSTRAINT category_id PRIMARY KEY (category_id)
);

Every product must have a category,
Since many (but not all) products have the same category I only want 1
table with unique categories.

To do the insert into the products table I need to retrieve or insert
the category_id in categories first.
Which means more code on my client app (if ($cat_id =
get_cat_id($cat)) }else { $cat_id = insert_cat($cat)})

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)

Alan
I donproducts.category to be a foreign key that points to the uniqie
category_id id in the want to keep I need to do get the cate

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

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