last id value plus 1 (not auto inc) 2

[tyutghf]

I am building a kind of shopping basket for our warehouse to be able to create orders.

The baskets table will feature

id - auto increment
basketid - this groups the products together
productid - id from the product table
pickerid - person responsible for picking
completed - 1 or 0 for picked or not

so each job will feature a common basketid to group them together, i.e. one basket for the picker

id | basketid | productid | pickerid | completed
1 1 15 4 0
2 1 23 4 0
3 1 34 4 0

What I am struggling with is when a new basket is created, how do I find the highest basketid and increment by 1 to create a new basket id?

Is this a good way to go about building a shopping cart?

Thanks

 

[vicvirk]

Try this:

sql = "select max(basketid) as cMax from myBasketTable"
set rs = conn.execute(sql)
cMax = rs("cMax")
rs.close
set rs = nothing
newBasketId = cint(cMax) + 1
response.write newBasketId

If that doesn't work, try this as your query

sql = "select top 1 basketid from myBasketTable order by basketid desc"

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[gmmastros]

In my opinion, you are taking the wrong approach. I mean... it could be worse, but this approach can lead to subtle problems that are hard to identify later (once you have forgotten about this).

The problem with this approach occurs when you have simultaneous updates. The two queries given by vicvirk will accurately return the largest BasketId currently in your table. But... consider what happens when there are multiple updates simultaneously. Since there is a measurable amount of time that passes between getting the max value and inserting a new row... it's possible that two users will get the same max value, and then insert the same basketid value, effectively combining two orders that should be separate.

There are ways to prevent this problem. In my opinion, the cleanest approach would be to have a separate table (Baskets) with an Identity column. There are probably other details about a basket that would be nice to store (dates, times, etc...). When you want to create a new order, you would first insert in to the Baskets table and capture the newly created id (using Select Scope_Identity()). Then use this value to insert in to your original table.

By using an identity column, you will avoid the problems associated with concurrent/simultaneous inserts.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vicvirk]

George's response makes 100% complete sense and you should make it more robust.

You could create a second table to handle basket id's or you could generate a guid to take care of the basket id - found this little gem online:

http://classicasp.aspfaq.com/general/how-do-i-generate-unique-guids-from-asp.html[/URL]]
<% 
    Function GetGuid() 
        Set TypeLib = CreateObject("Scriptlet.TypeLib") 
        GetGuid = Left(CStr(TypeLib.Guid), 38) 
        Set TypeLib = Nothing 
    End Function 
    Response.Write GetGuid() 
%>

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[tyutghf]

Thanks for the help guys.

So basically I have two options, create the unique identifier using the GUID code to create the basket id

OR

handle the basket id's in a different table with auto inc.

AS experienced programmers, which would you choose?

 

[gmmastros]

For the most part, it's a toss up.

Generally, I don't really care for guids. I find it to be a bit of a pain to work with them because it's a rather long string of letters and numbers. Looking at raw data and matching things up, to verify my queries are working properly, is a pain.

There are other, better reasons, to shy away from guids too. Mostly this applies to VERY LARGE tables with millions of rows. You see, GUIDS are random. When you have an index on a GUID, you end up getting a lot of page splits in your indexes. This problem is many times worse when the index is clustered. But... even with a non-clustered index, page splits can be bad for performance. You can alleviate the problem a little by altering the fill factor on your index, but you'll need to make sure you rebuild your indexes on a regular basis.

With INT IDENTITY columns, new data is always added at the end of the index (because it is sequential). This means there are never any page splits. Sure, you will occasionally need a new page (for the index), but that's a lot better than splitting a page.

For a high transaction system, the difference between GUIDS and INT IDENTITY can be huge. If this is for an internal site that have less than a couple thousand new orders per day, you will probably never notice a difference.

Clearly, I went on an on about this topic. Sorry. Since you asked, my recommendation is another table in the database.

I hope this makes sense.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[vicvirk]

Guids are great to use if you ever plan on merging tables together. An example is a company that has a table of customers in their db and they go out and purchase another company that has a customers db.

If both used GUIDs as the unique identifier for thier customers, it's a very safe bet that the data from one can be copied right over to the other.

If both used an auto-increment, then they would be stuck with two people with the same customer id.

In most cases, the auto-increment works fine - I use it almost all the time. One of the only time I use a guid is for sensitive information that I want to show the user on the screen - i.e.

mysite.com/customers/info.asp?cust_id=12

is not as "secure" as

mysite.com/customers/info.asp?cust_id={DAA99384-D82D-4718-B18A-6B294E716BA9}

Using the first scenerio, the user can simply change the "12" to a 10,11,13...and get information. The chances of them "guessing" a guid for another customer is very slim.

The flip side is asking a customer for their basket id and email address, it's a lot easier to remember:

myemail@mydomain.com
12

than it is to remember

myemail@mydomain.com
{DAA99384-D82D-4718-B18A-6B294E716BA9}

Like George said, it's a toss up and you can use either or - it's a lot easier to develop and do lookups with auto-increment INTs.



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[vicvirk]

thought about it a bit more and came up with this for you. You may have already figured it out, but just in case:

if you are assigning 1 "picker" per basket and not per item, and the "completed" flag is per basket and not per item, you could use a structure something like this:

baskets
basketid
pickerid
completed

items
itemid
basketid
productid

products
productid
productname

pickers
pickerid
pickername



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[tyutghf]

Really guys thanks a lot for your help. After doing lots of reading up (and digesting both your posts) I am going to go with the database approach ut once this is built I might do it again just for fun using guid to see if I understood it all correctly.

thanks again

 

[wvdba]

very good discussion here. i had a similar situation with order number generation. i use a order_num_table with only two columns: last_order_num and last_order_date.
anytime a customer places an order, i read order_num_table, get the last_order_num and add 1 to it and make the new order number for the customer. then i update order_num_table with the new order number. my order number looks like this:
NNNYYYYMMDDZZZZZ
N = just a number identifying department
yyyy = year
M = month
D = day
z = sequential number
hope this is a good way of doing it.