From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: alan <alan.miller3@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: insert
Date: Sat, 30 Jul 2011 17:24:21 +1200
Message-ID: <4E339585.9080301@archidevsys.co.nz> (raw)
In-Reply-To: <4E32CE5D020000250003F8FF@gw.wicourts.gov>
References: <2e208bc6-1bb8-4170-809f-77ab63a15bf4@l18g2000yql.googlegroups.com>
<4E32CE5D020000250003F8FF@gw.wicourts.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, Kevin.Grittner@wicourts.gov, alan.miller3@gmail.com
Subject: Re: insert
In-Reply-To: <4E339585.9080301@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox