Re: SQL Sequence use cases

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Pete,

In a sense I agree with you on sequence numbering. But there is one issue
where it will go wrong, imo.

I have my doubts in a parent-child relationship between tables. If the
parent and e the child bith get sequence numbering to make a record unique,
the child still needs the parent"s key and an additional key field to make
a proper direct link to a single record. Using a seqence number in the
child will not help, as the parent will not know what the child's sequence
in relation to the parent will be, and in an application to retrieve the
one and only record irt is more difficult, even when using SQL.

I am therefore not that font of creating unique and primary keys using
sequence numbering, as is propagated with the new possibilties. You still
have to create  traditional indices to link a parent to a single child
record. Methinks, the relational part of the RDBMS will slowly vanish and
replaced by the older network relationship model.

Just my thoughts, though.

With regards,
Carel Teijgeler



*********** REPLY SEPARATOR  ***********

On 25-11-2009 at 13:48 Pete Helgren wrote:

> As long as it is unique I think it can be quite useful.  Most of the
tables I work with outside of DB2 are keyed by sequence #.  If you work 
> with a Rail application, most of the unique keys on tables are assumed to
be sequences and named 'id'.  In fact, if you DO use sequences and 
> the column name is 'id', Rails pretty much works right out of the box.
>
> I have table on the i from a "legacy" application that has 8 fields that
make up the unique key.  If the table had a column that was a sequence, 
> my I/O would be SO much easier to accomplish.  Issuing an update to table
x where id = 34423 is a lot easier than update tablex where 
> key1='foo1' and key2 = 'bar1', etc....
>
> As for a "business" case, simplifying I/O could simplify development and
reduce costs.  In the MSSQL application I work with, every table has a 
> sequence column named "id" (even though it isn't a Rails app, it is a .Net
app).  Initially I found it confusing but as I work with the tables 
> more, my code can follow the pattern of using the sequence as the unique
key to the row making for greater consistency in SQL update statements 
> and my model code.
>
> Perhaps it boils down to coding and DB design preferences.  But, going
forward, all my new tables will have sequences that uniquely identify 
> the record.