Ticket system, conceptual help

[impact]

Hello,

I am trying to make a ticket system on my site. Each ticket will have a unique number (integers). Can you advice me what is the best way to number such things?

My initial idea was to go for serial numbering in mysql database. That way no duplicate ticket can be created but the problem is when some one quits half way through, it leaves a blank space in my database which I am not able to fill up in the later stage.

For example:
As I complete the first step of ticket creation and saves data into database, my ticket get row number 17. A friend of mine doing the same thing at the same time gets row number 18. Half way through I quit, deleting row number 17. But my friend completes it. And when the next person completes it he gets row number 19. This is leaving a void row number in my database.

What would be your advice on this? Will advice me to count the total rows in mysql and to that manually add 1 and save as ticket number?

Any help will be appreciated.

Thank you,

[jspeed]

There should not be a "void" row if someone exits the ticket creation script. As in, you shouldn't allow that in your code. I wouldn't submit anything to the DB until all the forms are completed/scrubbed/validated etc. or you would end up with a database full of holes.

I'm sure there are plenty of ways to handle it, but if you are worried about the auto-increment field, you can always reset it before adding to it:

ALTER TABLE tablename AUTO_INCREMENT = 1

That will tell the next auto-incremented field to be + 1 the current largest number.

Hope that helps.

[httpwebwitch]

I disagree with jspeed. Void rows are fine, as long as they can't be confused with active rows. Using an autoincrementing ID to "reserve" an ID number will prevent collisions and race conditions - just as you planned.

let your db schema have a column named "active" or "valid" or something like that. Its default value is 0. When the user completes the ticket and saves it, you can update that to 1. When selecting active tickets, add "WHERE active = 1".

Your database will have "holes". does it matter? You shouldn't care if your ticket numbers are perfectly sequential without gaps. Is anyone going to be irritated if the tickets are numbered 512, 513, 515, 516... ? Will they ask "what happened to 514?"

If you want to clean up abandoned rows later, you could put a timestamp column in, and DELETE FROM table WHERE active = 0 AND timestamp is something a few days ago. Run it on a daily cron.

This schema also allows your interface to save Drafts.

[Mr Bo Jangles]

I agree with httpwebwitch.

[g1smd]

I wouldn't use pure numbers for tickets. It exposes how many customers you haven't got.

I would use some element of the date as part of the number and include the year at the beginning, ensuring the numbers can never repeat.

[jspeed]

Correct, it doesn't matter to anyone if the numbers are sequential in the ticket field. My only point was, why would you not validate the submitted data before the ticket number is even created? Why go through the "active" field or putting a timestamp in, and having to check for incomplete submitted data to delete? Wouldn't you do that before it was inserted in the DB?

[impact]

Thank you all for the help.

@ jspeed
In my form there are many fields. Each fields have different criteria for it to be accepted. Since I am not very good in php or ajax, i broke my form into many pages. This helps me to process small submission at a time and report any error.

Having said this, when the user completes the first page, I must store the data some where. I could have done so in $_session or in a temporary database but well, i dint do it.

Now when I am making a trial of a small part of the script which I have completed. I came across this error.

@ httpwebwitch
Yes, that sounds logical. This method will allow me to remove all void rows easily or count the total tickets that are in database.

Thank you all once more.