Using ASP.net I'm trying to set up a basic online shop. I've set up a database in MySql that has a variety of tables in it.
(Customers, Suppliers, Products etc)
I've set up a table called Quotes in order to keep track of "online" quotes like this:


As people browse through products and prices they "build" their own quote by selecting a few items.
They will then click a button marked "Get Quote" which will take them to the Quote page.

This page I want to structure like a conventional quote, and in order to get a Quote number, I'm running a select statement like this:


EDIT:
Then I add 1 in the ExecuteScalar statement to get the next Quote number:


There will be another button here to "Accept Quote" which will write all details to the "Quote" table and have an option for the user to Print.

This works fine at the moment, but I can forsee the following problem:

If 2 or more users click the "Get Quote" button at around the same time the select statement will return the same quote number for all of them. This will cause issues with non-unique QuoteIDs.

Do I need to lock the number somehow?
The problem with doing this is that if the 2nd user rejects his quote there is an unused quote number in the DB.

Is there a problem with my DB structure?

Any thoughts would be appreciated.

This post has been edited by Footsie: 29 Mar, 2008 - 05:04 AM

Why not save yourself a lot of trouble and just use the AUTO_INCREMENT Attribute on your ID field. It will the automatically increment the last ID by 1, ensuring unique ID's

My QuoteID is set to auto increment, and as the primary key.
But the values only get written to the table at the end when the user clicks "Accept Quote".

The issue is with the sequence:


Now, if another user or 3 execute point 1> before the first user clicks "Accept", and writes the values to the DB in point 4> The same quote number will be displayed.

I hope that explains it better.

This post has been edited by Footsie: 29 Mar, 2008 - 08:19 AM

Why are you making this so difficult on yourself (unless this is a homework assignment, and if it is then your instructor is making this harder than it should be). What you do is before entering the quote information check to be sure the same quote ID isn't already present, if it is then increment the current ID by 1 then insert.

That will cover your problem, as 2 people cannot insert data at the same exact time (which is highly unlikely as the timestamp of the insertion is calculated including milliseconds, which makes it virtually impossible to 2 records to be inserted at the same exact time).


So:

• Get the last ID before giving the quote
• If the user selects "Save Quote":
  
  • Check to database to see if the ID this user has is already there
    
  • If it is
    
    • Get the current ID and increment by 1
      
    • Insert the quote data
  • If it doesn't already exist insert the data

Hey Psycho.
No, not a homework assignment. I wish it was - it would mean I am still at school / college and starting this stuff a lot younger!
Just me being hard on myself.
I don't know why I didn't think of checking twice - that should most definitely solve my almost non-existent problem.

And Psycho, thanks again for the help.

I've been told(although I can't guarantee) that running LAST_INSERT_ID() will return the last inserted ID for that connection - so in your case, if two people ran LAST_INSERT_ID() at the same time, they would each get their specific last inserted ID's. You may want to test it to be sure, but that's what I've been told.

you can use a kludge: have a separate table with one autoincrement field, where you just insert a new record on the start, that gives you the unique id.