0

I have registration form for users. They can registrate to the tour and I need to generate unique UID for each of their registration. I use ID as primary key and its autoincrement, but its only for system purpose only. I need to generate another UID which is used for pairing payments from bank account.

The format of UID I need to generate is {TOUR_NUMBER}{REGISTRATION_NUMBER}, so the numerical series can be like 59001, 59002, 59003 and so on (59 is number of the tour).

Currently I use this scenario.

1) count how many users registered to certain tour already
2) increment that number
3) with while() check if that incremented number is not already used
4a) if not, save the registration
4b) if it is, increment number again and continue with 3)

Problem is, that if there is many users at the same time, system generate the same UID for more users. I've tried to start transaction before point 1) and commit them after 4a) but it did not help.

--- EDIT ---

Maybe I did not explain my problem sufficiently. Let me try it again. Here are simplified tables I have. Tour_id and registration_id columns are auto-incremented.

tour_id name
1 England
2 Germany
registration_id name tour_id payment_id
1 George 1 1001
2 John 1 1002
3 Jane 1 1003
4 Luke 2 2001

payment_id is generated from tour_id and incremented number of total users registrated to the certain tour. The payment_id format can not be changed, it must be the way it is now, unfortunatelly. The value is used primarly for the payment pairing so it must be connected to certain user from the beginning and saved within user's tour registration.

I just need to ensure, that the payment_id will not be the same, which happend now from time to time, because delays in executing queries in my scenario above I guess. If it happends, the table looks like the table below, which I need to avoid - George and John have the same payments_id because they did registration at the same time. And 1002 is missing.

registration_id name tour_id payment_id
1 George 1 1001
2 John 1 1001
3 Jane 1 1003
4 Luke 2 2001
sylar32
  • 197
  • 3
  • 16
  • You could just use a calculated column in the database to concatenate the tour number and the autoincrement ID. But why this specific format anyway? I'm assuming this isn't a bank account number. Why can't you just use the autoincrement on its own? Does this have to be sent to another application which only accepts a certain format, or something? It would be good to understand the constraints which led to this requirement. – ADyson Mar 28 '22 at 13:14
  • @ADyson, Generated columns in MySQL cannot reference the auto-increment column. Try it. – Bill Karwin Mar 28 '22 at 13:21
  • @BillKarwin well it could be a calculation in a view or query then, I was using the term loosely I suppose, apologies. – ADyson Mar 28 '22 at 13:25
  • As you can see, this question has been discussed on SO many times! I would take the hint from @ADyson (which is also echoed by Erwin's answer in the 2nd duplicate - https://stackoverflow.com/a/34572959/5389997) and refrain from doing this. Generating the reference either in a query or a view is better than trying to create a new id field. – Shadow Mar 28 '22 at 13:49
  • I edited my original post and add more informations to clarify my problem. I appreciate your answers, but I think there was missunderstanding about my problem (my bad I guess, I did not explain it good enought). – sylar32 Mar 28 '22 at 14:52

0 Answers0