Re: data dependent sequences?

"Andrew Sullivan" <ajs@crankycanuck.ca> wrote in message news:20070717150046.GC21688@phlogiston.dyndns.org...
> On Tue, Jul 17, 2007 at 07:34:26AM -0600, Stuart wrote:
> > I am not looking for gapless sequences.  The reason I
> > want to do this is the "typ" column is actually an indicator
> > of the source of the rest of the infomation in the row.
> 
> Why do you need the sequence to be 1. . .n for each typ, then?  If
> they're just there to preserve order, one sequence will work just
> fine.  Otherwise, I think you have a normalisation problem.

I probably shouldn't have indicated that 'typ' was part of
the PK, or named the other column 'id'.  There is actually a 
separate (surrogate) PK, and there is a unique index on the
on (id,typ) only to prevent accidental dupicates.  So 'id' 
is not really structually important -- it is a value that 
exists soley for the UI.  In the app, the user can explicity 
request an explicit 'id' value.  My desire to use a sequence 
to assign them is to handle the 99% common case where the user 
doesn't care about assigning a specific id, and just wants 
the "next" resonable value, consistent with the other values 
for that typ row.  If there are 3 rows of typ=20 and 2000000
rows of typ=21, I don't want the next typ=20 row to get an
id of 2000004, when the other rows have values of 1,2,3.
This is simply a user expectation, based on existing data, 
that I can't change.  I would just prefer to implement it 
in the database if possible rather than than the app. 
Hope I have clarified a little :-)