Hold record id for ticketing system problem

Question

OK we have a custom ticketing system developed in PHP/PostgreSQL. How it works now is that information is entered, click submit, ticket number is shown. What They would like, Ticket number shown, enter information, click submit.

Now I know I can just create a blank record and use that record id then do an update when the user submits.

I wanted to know if there would be a better/alternative way in doing this?

Some key points:

• record id is the pk, auto increments
• record id's must be in order, no skipping record id numbers

The problem is a end user could start the ticket id process and not click submit which would result in a blank record. Which might be the best way to proceed.

any ideas or is this the best approach?

EDIT: I understand all the gripes and what I need to do to complete the requested action, looks like I'm just going to be dealing with blank records. It shouldn't happen to often but I wanted to see if anyone had to deal with this before. Thanks for the input

Answer 1

There is no practical way to do this.

It's not prudent to lock the tables or use transactions as the app must wait for additional user input. That means tickets with info will have gaps between them. This is a silly requirement on the client's part. Your managers should have done a better job explaining why getting the ticket number afterward is the way to go (race conditions):

Obtaining the next auto-increment id requires access to a resource. This means there must be control to that resource. Imagine implementing this approach at a deli. One person takes a number and decides what they want. Another person tries to grab a number. You must somehow stop them (lock tables) or your numbers might be out of order should the first person leave! That is of course a silly way to run a business. If the first person decides not to order anything, he should be able to just throw the ticket away (you've got over 4 billion tickets -- and can even increase that -- so you'll be fine).

Answer 2

You could do something like Hi-Lo Algorithm although that negates your second keypoint unless you use a new column to store the HiLo

You could also not rely on the Record ID and instead use a hash algorithm or something of that nature that can be unique across the rows.

Otherwise you'd have be okay with unfinished tickets or create a cron job that clears out the unfinished tickets.

1. Create new table like old table
2. Insert into new table all data from the old table that excludes the blank records
3. Update a table with the new tables's name
4. Drop the old table
5. Pull down the variable to figure out which table to use when querying on the table (annoying, I know)

This does work on a large-scale, though, despite mucking with the code.

Answer 3

record id's must be in order, no skipping record id numbers

There is a problem: When user 1 start a ticket he get e.g. the number 234, after that user 2 start also a ticket and get the number 235, but when user 1 cancel his ticket then the number 234 is skipped

ok you could check again before save but then you have the problem that the number which is shown at the beginning of a ticket is maybe not the final number...

p.s.: i have not seen any ticket systems which shows you the final ticket number at the start of creating a new ticket...

Answer 4

The requirement to have no ticket numbers skipped makes me think that the ticket numbers are essentially a range of valid values that your generated tickets must fall in to. If that's the case I'd suggest separating your record's PK from the ticket number and having a separate ticket reservation table that essentially puts a lock against a ticket number with a timestamp. The blank record is inserted and tied to a reservation when they first arrive, locked in when they finish or falls in to the abandoned state after the timeout period expires and the ticket number gets picked up by a future request.

Hope that helps.