Re: SQL Sequence use cases

I agree with the parent-child issue. That is probably where the "simplicity" factor begins to break down. That is, in fact, where most of my confusion stemmed. But, with proper foreign key relationships in place, it seems to sort itself out.

I am rather new to the whole concept of using a sequence as a unique primary key so I haven't had a chance to thoroughly vet it. Rails introduced me to it and then this MSSQL database I had to interface with gave me more practical experience with it. Ask me again in another year! I still have some learning to do in this area.

Pete


Carel Teijgeler wrote:

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.