I have a slight problem with Sql 7.0.  I have 2 tables, one is called 

orders and the other orderCart.  Orders generates a unique ID for each 

order, and orderCart has the specific items ordered, and an order ID for 

all items on the same order.  So naturally I enter an order for 3 items, 

and 3 items are inserted into orderCart with an ID of say 6.  I do another 

order, 2 items, a new orderID of 7 is generated, and 2 items added to 

orderCart with an orderID of 7.  However, Sql 7.0 doesn't list the items 

with orderID=6 first, then orderID=7 next...it seems to jumble up all of 

these randomly until I have 2 items from orderID=7, 1 from orderID=6, 3 

from orderID=18, 2 from orderID=4, etc.  Shouldn't these fields be inserted 

in order so that you have them all in order and together?  If not, is there 

any way I can get sql 7.0 to do this?  The button to sort ascending or 

descending when I'm viewing the table is greyed out so I can't use that 

method.

Simple answer: No (though I could be missing the point of your question)



How the database stores the data physically should not be a concern of the

end user - provided that the data can be retrieved again and sorted

appropriately...



(eg Imagine that I insert 6 records, delete the middle 2, then insert 5 two

more. If you want everything in order, with no space wasted then the

database would have to delete the middle two, move all the remaining records

up to fill in the empty space, then insert the next 5 etc which is a waste

of effort. As long as there is some kind of index on the key, the database

can find the record easily enough, and all you need do is have an ORDER BY

field1 ASC in your SQL statement to get the records in the correct order for

display)



Cheers

Ken



----- Original Message -----

From: "Ben Lew" 

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, July 04, 2000 2:24 AM

Subject: [asp_databases] order that fields are inserted into SQL 7.0

(they're not being inserted in the order I put them in)





> I have a slight problem with Sql 7.0.  I have 2 tables, one is called

> orders and the other orderCart.  Orders generates a unique ID for each

> order, and orderCart has the specific items ordered, and an order ID for

> all items on the same order.  So naturally I enter an order for 3 items,

> and 3 items are inserted into orderCart with an ID of say 6.  I do another

> order, 2 items, a new orderID of 7 is generated, and 2 items added to

> orderCart with an orderID of 7.  However, Sql 7.0 doesn't list the items

> with orderID=6 first, then orderID=7 next...it seems to jumble up all of

> these randomly until I have 2 items from orderID=7, 1 from orderID=6, 3

> from orderID=18, 2 from orderID=4, etc.  Shouldn't these fields be

inserted

> in order so that you have them all in order and together?  If not, is

there

> any way I can get sql 7.0 to do this?  The button to sort ascending or

> descending when I'm viewing the table is greyed out so I can't use that

> method.

Yes, that makes sense, Ken.  When you say there needs to be some kind of

index on the key, can that be a foreign key (i.e., the orderID which is a

unique key generated in another table that is linked to the orderCart table)

or does it have to be a key unique to this table?



Thanks,



Ben



----- Original Message -----

From: "Ken Schaefer" 

To: "ASP Databases" <asp_databases@p...>

Sent: Tuesday, July 04, 2000 3:12 AM

Subject: [asp_databases] Re: order that fields are inserted into SQL 7.0

(they're not being inserted in the order I put them in)





> Simple answer: No (though I could be missing the point of your question)

>

> How the database stores the data physically should not be a concern of the

> end user - provided that the data can be retrieved again and sorted

> appropriately...

>

> (eg Imagine that I insert 6 records, delete the middle 2, then insert 5

two

> more. If you want everything in order, with no space wasted then the

> database would have to delete the middle two, move all the remaining

records

> up to fill in the empty space, then insert the next 5 etc which is a waste

> of effort. As long as there is some kind of index on the key, the database

> can find the record easily enough, and all you need do is have an ORDER BY

> field1 ASC in your SQL statement to get the records in the correct order

for

> display)

>

> Cheers

> Ken

>

> ----- Original Message -----

> From: "Ben Lew"

> To: "ASP Databases" <asp_databases@p...>

> Sent: Tuesday, July 04, 2000 2:24 AM

> Subject: [asp_databases] order that fields are inserted into SQL 7.0

> (they're not being inserted in the order I put them in)

>

>

> > I have a slight problem with Sql 7.0.  I have 2 tables, one is called

> > orders and the other orderCart.  Orders generates a unique ID for each

> > order, and orderCart has the specific items ordered, and an order ID for

> > all items on the same order.  So naturally I enter an order for 3 items,

> > and 3 items are inserted into orderCart with an ID of say 6.  I do

another

> > order, 2 items, a new orderID of 7 is generated, and 2 items added to

> > orderCart with an orderID of 7.  However, Sql 7.0 doesn't list the items

> > with orderID=6 first, then orderID=7 next...it seems to jumble up all of

> > these randomly until I have 2 items from orderID=7, 1 from orderID=6, 3

> > from orderID=18, 2 from orderID=4, etc.  Shouldn't these fields be

> inserted

> > in order so that you have them all in order and together?  If not, is

> there

> > any way I can get sql 7.0 to do this?  The button to sort ascending or

> > descending when I'm viewing the table is greyed out so I can't use that

> > method.

>

Getting a good book on databases will explain the balancing act involved in

creating indexes, and what fields you should index and what you shouldn't.



However you should alway index your Primary Key, as this is the method that

your database will use to find records in the current table. Foreign Keys

are used to create referential integrity between tables, primary keys are

used to locate records within the table.



You should always have a primary key for each table, and unless the

relationship is 1-1, the primary key *should not* be a foreign key.



Cheers

Ken



----- Original Message -----

From: "Ben Lew" 

To: "ASP Databases" <asp_databases@p...>

Sent: Wednesday, July 05, 2000 1:21 AM

Subject: [asp_databases] Re: order that fields are inserted into SQL 7.0

(they're not being inserted in the order I put them in)





> Yes, that makes sense, Ken.  When you say there needs to be some kind of

> index on the key, can that be a foreign key (i.e., the orderID which is a

> unique key generated in another table that is linked to the orderCart

table)

> or does it have to be a key unique to this table?