Is There a Better Way? (Incrementing PK Column)  
Author Message
bobbrighton





PostPosted: Thu Jun 10 15:50:02 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column)

Hi Group,

I have a master and detail table like so,
Master ( ID int PK, [...] )
Detail (MasterID int PK, DetailNum int PK, [...])

The idea is that DetialNum will start at 1 and count up for each MasterID.
Example:
{1,1,[...]}, {1,2,[...]}, {1,3,[...]}, {2,1,[...]}

I want to add records to the Detail table like so:
INSERT INTO Detail (MasterID) VALUES (1)
INSERT INTO Detail (MasterID,DetailNum) VALUES (2,2)
INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
SomewhereElse

To do this, I created an INSTEAD OF INSERT trigger on Detail that does the
following:


DetailNum being updatable

it should be


So is there a more efficient way of handling this? I'm aware that I can use
a stored procedure. But with that, I don't know how I would do something
like:
INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
SomewhereElse

I'd like to use (something like this) as an approach for a number of
systems. Opinions? Ideas?

Thanks,
Alex Papadimoulis

SQL Server165  
 
 
Aaron





PostPosted: Thu Jun 10 15:50:02 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) Can't you derive the ranking per ID when you run your SELECT queries?

One problem with storing the data is that you essentially have to update the
whole table when you change, add, or remove a row. In a high-volume system,
I would much rather take this hit at reporting time (which can be pushed off
to a reporting system anyway) than at transaction time.

--
http://www.aspfaq.com/
(Reverse address to reply.)






> Hi Group,
>
> I have a master and detail table like so,
> Master ( ID int PK, [...] )
> Detail (MasterID int PK, DetailNum int PK, [...])
>
> The idea is that DetialNum will start at 1 and count up for each MasterID.
> Example:
> {1,1,[...]}, {1,2,[...]}, {1,3,[...]}, {2,1,[...]}
>
> I want to add records to the Detail table like so:
> INSERT INTO Detail (MasterID) VALUES (1)
> INSERT INTO Detail (MasterID,DetailNum) VALUES (2,2)
> INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
> SomewhereElse
>
> To do this, I created an INSTEAD OF INSERT trigger on Detail that does the
> following:


> DetailNum being updatable

what
> it should be

>
> So is there a more efficient way of handling this? I'm aware that I can
use
> a stored procedure. But with that, I don't know how I would do something
> like:
> INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
> SomewhereElse
>
> I'd like to use (something like this) as an approach for a number of
> systems. Opinions? Ideas?
>
> Thanks,
> Alex Papadimoulis
>
>


 
 
Alex





PostPosted: Thu Jun 10 16:02:46 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) Aaron,

I suppose I could do something like:
@id = ISNULL(SELECT TOP 1 DetailNum+1..., 1)

but then I'd have to do that over and over again in client code. which is
what i was trying to avoid.

And I should clarify -- the whole table doesn't need to be updated on
delete. If Detail 3 of 6 is deleted, the next DetailNum will still be 7. I
just want a simple way (sans SPs, I think) to insert rows into the Details
table ... as easy as using an IDENTITY.

--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis




> Can't you derive the ranking per ID when you run your SELECT queries?
>
> One problem with storing the data is that you essentially have to update
the
> whole table when you change, add, or remove a row. In a high-volume
system,
> I would much rather take this hit at reporting time (which can be pushed
off
> to a reporting system anyway) than at transaction time.
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>


> > Hi Group,
> >
> > I have a master and detail table like so,
> > Master ( ID int PK, [...] )
> > Detail (MasterID int PK, DetailNum int PK, [...])
> >
> > The idea is that DetialNum will start at 1 and count up for each
MasterID.
> > Example:
> > {1,1,[...]}, {1,2,[...]}, {1,3,[...]}, {2,1,[...]}
> >
> > I want to add records to the Detail table like so:
> > INSERT INTO Detail (MasterID) VALUES (1)
> > INSERT INTO Detail (MasterID,DetailNum) VALUES (2,2)
> > INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
> > SomewhereElse
> >
> > To do this, I created an INSTEAD OF INSERT trigger on Detail that does
the
> > following:


with
> > DetailNum being updatable

> what
> > it should be

> >
> > So is there a more efficient way of handling this? I'm aware that I can
> use
> > a stored procedure. But with that, I don't know how I would do something
> > like:
> > INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
> > SomewhereElse
> >
> > I'd like to use (something like this) as an approach for a number of
> > systems. Opinions? Ideas?
> >
> > Thanks,
> > Alex Papadimoulis
> >
> >
>
>


 
 
Aaron





PostPosted: Thu Jun 10 16:04:54 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) You can't really have a complex multiple-column IDENTITY like that.

Oh, and generating row numbers does not require a variable and row-by-row
execution in that way. See http://www.aspfaq.com/2427

--
http://www.aspfaq.com/
(Reverse address to reply.)






> Aaron,
>
> I suppose I could do something like:


> but then I'd have to do that over and over again in client code. which is
> what i was trying to avoid.
>
> And I should clarify -- the whole table doesn't need to be updated on
> delete. If Detail 3 of 6 is deleted, the next DetailNum will still be 7. I
> just want a simple way (sans SPs, I think) to insert rows into the Details
> table ... as easy as using an IDENTITY.
>
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
>


> > Can't you derive the ranking per ID when you run your SELECT queries?
> >
> > One problem with storing the data is that you essentially have to update
> the
> > whole table when you change, add, or remove a row. In a high-volume
> system,
> > I would much rather take this hit at reporting time (which can be pushed
> off
> > to a reporting system anyway) than at transaction time.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >


> > > Hi Group,
> > >
> > > I have a master and detail table like so,
> > > Master ( ID int PK, [...] )
> > > Detail (MasterID int PK, DetailNum int PK, [...])
> > >
> > > The idea is that DetialNum will start at 1 and count up for each
> MasterID.
> > > Example:
> > > {1,1,[...]}, {1,2,[...]}, {1,3,[...]}, {2,1,[...]}
> > >
> > > I want to add records to the Detail table like so:
> > > INSERT INTO Detail (MasterID) VALUES (1)
> > > INSERT INTO Detail (MasterID,DetailNum) VALUES (2,2)
> > > INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
> > > SomewhereElse
> > >
> > > To do this, I created an INSTEAD OF INSERT trigger on Detail that does
> the
> > > following:


> with
> > > DetailNum being updatable

> > what
> > > it should be

> > >
> > > So is there a more efficient way of handling this? I'm aware that I
can
> > use
> > > a stored procedure. But with that, I don't know how I would do
something
> > > like:
> > > INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...] FROM
> > > SomewhereElse
> > >
> > > I'd like to use (something like this) as an approach for a number of
> > > systems. Opinions? Ideas?
> > >
> > > Thanks,
> > > Alex Papadimoulis
> > >
> > >
> >
> >
>
>


 
 
Alex





PostPosted: Thu Jun 10 16:23:54 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) Well it's not really and IDENTITY. It's a sequencer that starts at one and
increments by one for each MasterID. Because this is a Data-Integrity rule
(it's not, IMO, something the client should worry about having to set),
that's why I'm thinking it belongs in the DB.

I read the article you sent, but that's primary for Retrieving data -- I'm
looking to have it increment the DetailNum when a new val is inserted.

Or is there a better way of me doing this?

--
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis




> You can't really have a complex multiple-column IDENTITY like that.
>
> Oh, and generating row numbers does not require a variable and row-by-row
> execution in that way. See http://www.aspfaq.com/2427
>
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
>
>


> > Aaron,
> >
> > I suppose I could do something like:


> > but then I'd have to do that over and over again in client code. which
is
> > what i was trying to avoid.
> >
> > And I should clarify -- the whole table doesn't need to be updated on
> > delete. If Detail 3 of 6 is deleted, the next DetailNum will still be 7.
I
> > just want a simple way (sans SPs, I think) to insert rows into the
Details
> > table ... as easy as using an IDENTITY.
> >
> > --
> > Alex Papadimoulis
> > http://weblogs.asp.net/Alex_Papadimoulis
> >
> >


> > > Can't you derive the ranking per ID when you run your SELECT queries?
> > >
> > > One problem with storing the data is that you essentially have to
update
> > the
> > > whole table when you change, add, or remove a row. In a high-volume
> > system,
> > > I would much rather take this hit at reporting time (which can be
pushed
> > off
> > > to a reporting system anyway) than at transaction time.
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >

message

> > > > Hi Group,
> > > >
> > > > I have a master and detail table like so,
> > > > Master ( ID int PK, [...] )
> > > > Detail (MasterID int PK, DetailNum int PK, [...])
> > > >
> > > > The idea is that DetialNum will start at 1 and count up for each
> > MasterID.
> > > > Example:
> > > > {1,1,[...]}, {1,2,[...]}, {1,3,[...]}, {2,1,[...]}
> > > >
> > > > I want to add records to the Detail table like so:
> > > > INSERT INTO Detail (MasterID) VALUES (1)
> > > > INSERT INTO Detail (MasterID,DetailNum) VALUES (2,2)
> > > > INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...]
FROM
> > > > SomewhereElse
> > > >
> > > > To do this, I created an INSTEAD OF INSERT trigger on Detail that
does
> > the
> > > > following:


> > with
> > > > DetailNum being updatable

to
> > > what
> > > > it should be

> > > >
> > > > So is there a more efficient way of handling this? I'm aware that I
> can
> > > use
> > > > a stored procedure. But with that, I don't know how I would do
> something
> > > > like:
> > > > INSERT INTO Detail (MasterID, [...]) SELECT 1 AS MasterID, [...]
FROM
> > > > SomewhereElse
> > > >
> > > > I'd like to use (something like this) as an approach for a number of
> > > > systems. Opinions? Ideas?
> > > >
> > > > Thanks,
> > > > Alex Papadimoulis
> > > >
> > > >
> > >
> > >
> >
> >
>
>


 
 
anonymous





PostPosted: Thu Jun 10 16:36:01 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) Considering that value of deletes do not change the forward pattern it seems to me you are just after knowing the order of the inserts with relation to the same Master ID.

If that is the case why not do either of the following which gives you that still.

Opt 1) Make the DetailNum column a datetime and use the getdate() function as the value. This will allow you to be able to programatically determine occurance order as long as now more than one occurrance can happen in the same instance.

Opt 2) Make the DetailNum column an Identity column, this means it will still provide a relational order of the occurance of the Master ID insert without issue and you can still programatically determine the ordinal position if needs be.
 
 
Aaron





PostPosted: Thu Jun 10 19:11:23 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) > I read the article you sent, but that's primary for Retrieving data

Well, can you explain what use the data is being in the database, unless you
are going to retrieve it and display it at some point?


 
 
Joe





PostPosted: Thu Jun 10 21:12:41 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) >> I have a master and detail table like so, <<

1) The terms "master" and "detail" come from the old navigational
databases, likew TOTAL, IDMS, IMS, etc. In SQL we have "referenced" and
"referencing" tables, which are very different.

2) Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Your personal language is useless to anyone else; you two
Primary keys, while SQL allows only one per table.

>> I want to add records [sic] to the Detail table like so:<<

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.

>> To do this, I created an INSTEAD OF INSERT trigger on Detail that
does the following:



with DetailNum being updatable


what it should be



Let's see, you have Triggers, cursors, loops and temp tables -- that is
all the non-relational features you could use in T-SQL. Why don't you
just use an old magnetic tape file system instead?

>> The idea is that DetialNum will start at 1 and count up for each
MasterID.<<

Yep! Just like an old IMS database, with sequentail access to the
detail record chains!! This is so FUNDAMENTALLY wrong in an RDBMS, I am
not sure where to begin.

What is the generalized "Master_id"? An invoice number? some industry
standard? What? Likewise, I would assume that a good programmer would
use a verififable code for the details (UPC, EAN, GTIN, etc.), but I
would bet you are copying a paper form, line for line into an RDBMS. I
have seen this****up so many, many times.

>> I'd like to use (something like this) as an approach for a number of
systems. Opinions? Ideas? <<

Stop writing SQL, learn the basics and apologize to the employers you
have hurt. Get someone to re-design the system and to build correct
stored procedures -- no cursors, no sequential numbering, few is any
triggers, etc.

--CELKO--


*** Sent via Devdex http://www.hide-link.com/ ***
Don't just participate in USENET...get rewarded for it!
 
 
Alex





PostPosted: Fri Jun 11 13:13:03 CDT 2004 Top

SQL Server Developer >> Is There a Better Way? (Incrementing PK Column) Joe, a pleasure to be "schooled" by the expert. I've read (much of) your SQL
For Smarties and like your alternative to Adjacency Lists. The only chapter
missing was how to convince the boss to drop the painfully horrid practices
;-).

Anyways, I don't have any DDL scripts as this is a this is a general design
question. So, I'll try a real-world example from a system I developed a
while ago, but am wondering if there was a better way.

Orders have an OrderNum generated by a business rule (Yr +
DayOfYear+Type+Seq) and one or more Line Items.item number.
A LineItem has a sequentially number and zero or more Status entries.
A Status consists of a Status Code (processed, picked, delayed, etc) and a
Date.

For the LineItems table, the key was {OrderNum+ItemSeqNum}. It could not be
{OrderNum+Product} because an order could have two duplicate line items
(business rule). And Status table was {OrderNum+ItemSeqNum+StatusCode}.

Order creation was done through a number of different clients. One clients
was unwilling to use stored procedures, another wanted batch import of
orders from another system that didn't have line numbers (INSERT INTO
LineItems SELECT * FROM MyLineItems), so I ended up putting a trigger on the
Line Items table that made INSERTing the LineNum optional. It would sequence
it in the manner I had described.

So to bring back to my original question(s). Was there a better way of doing
that? In the future, how should i handle the situation where I need to
generate Sequential LineNumbers based off of a Order table? Should that be
done in client code or should it be in a Stored Pro such as


Thanks.
--
Alex Papadimoulis
http://www.hide-link.com/




> >> I have a master and detail table like so, <<
>
> 1) The terms "master" and "detail" come from the old navigational
> databases, likew TOTAL, IDMS, IMS, etc. In SQL we have "referenced" and
> "referencing" tables, which are very different.
>
> 2) Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, datatypes, etc. in your
> schema are. Your personal language is useless to anyone else; you two
> Primary keys, while SQL allows only one per table.
>
> >> I want to add records [sic] to the Detail table like so:<<
>
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS, so "first", "next" and "last" are totally
> meaningless.
>
> >> To do this, I created an INSTEAD OF INSERT trigger on Detail that
> does the following:

>

> with DetailNum being updatable
>

> what it should be
>

>
> Let's see, you have Triggers, cursors, loops and temp tables -- that is
> all the non-relational features you could use in T-SQL. Why don't you
> just use an old magnetic tape file system instead?
>
> >> The idea is that DetialNum will start at 1 and count up for each
> MasterID.<<
>
> Yep! Just like an old IMS database, with sequentail access to the
> detail record chains!! This is so FUNDAMENTALLY wrong in an RDBMS, I am
> not sure where to begin.
>
> What is the generalized "Master_id"? An invoice number? some industry
> standard? What? Likewise, I would assume that a good programmer would
> use a verififable code for the details (UPC, EAN, GTIN, etc.), but I
> would bet you are copying a paper form, line for line into an RDBMS. I
> have seen this****up so many, many times.
>
> >> I'd like to use (something like this) as an approach for a number of
> systems. Opinions? Ideas? <<
>
> Stop writing SQL, learn the basics and apologize to the employers you
> have hurt. Get someone to re-design the system and to build correct
> stored procedures -- no cursors, no sequential numbering, few is any
> triggers, etc.
>
> --CELKO--
>
>
> *** Sent via Devdex http://www.hide-link.com/ ***
> Don't just participate in USENET...get rewarded for it!