Message-ID: <1319.192.168.0.64.1071648485.squirrel@mercury.wardbrook.com>
In-Reply-To: <1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.com>
References: <3FDFAD8F.5080404@digitalflex.net>
	<1294.192.168.0.64.1071647844.squirrel@mercury.wardbrook.com>
Date: Wed, 17 Dec 2003 08:08:05 -0000 (GMT)
Subject: Re: Sequence question
From: "John Sidney-Woollett" <johnsw@wardbrook.com>
To: johnsw@wardbrook.com
Cc: "Anthony Best" <abest@digitalflex.net>,
	pgsql-general@postgresql.org
Reply-To: johnsw@wardbrook.com
User-Agent: SquirrelMail/1.4.2 [CVS]
MIME-Version: 1.0
Content-Type: text/plain;charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Importance: Normal

Sorry I should have added that the trigger needs to create a new keyword
record if the join in the trigger fails to locate the keyword in the
keyword table.

Hopefully you can create the trigger yourself.

The keyword table is effectively a distinct list of all keywords inserted
into the data table with the associated last allocated sequence number for
the keyword.

John

John Sidney-Woollett said:
> How about using two tables; one to hold the keyword and its (last
> allocated) sequence value, and the second to store your data as below.
>
> create table Keyword (
>   keyword varchar(32),
>   sequence integer,
>   primary key(keyword)
> )
>
> create table Data (
>   id serial,
>   sequence int,
>   keyword varchar(32),
>   text text
> )
>
> Add a trigger to the Data table for Insert so that it joins to the
> (parent) keyword table and increments the keyword.sequence value, and
> places that into the Data.sequence value.
>
> You will get 'holes' in the keyword sequencing when you delete data from
> the Data table. If that's a problem then you will need an alternative
> design.
>
> Hope that helps.
>
> John Sidney-Woollett
>
> Anthony Best said:
>> I'm working on an idea that uses sequences.
>>
>> I'm going to create a table like this:
>>
>> id serial,
>> sequence int,
>> keyword varchar(32),
>> text text
>>
>> for every keyword there will be a uniq sequence for it eg:
>>
>> id, sequence, keyword
>> 1, 1, foo, ver1
>> 2, 1, bar, bar ver1
>> 3, 2, foo, ver2
>> 4, 2, bar, bar ver2
>> etc...
>>
>> I could have one sequence for all keyword which would be 1,3, etc... I
>> would be prefer to have them in sequence.  I'm sure someone has ran into
>> this before, any ideas?
>>
>> Anthony.
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>