I have an application where I need to generate random PINs for for prepaid scratch cards. The PIN and other attributes for the scratch cards will be inserted in to a database.
- Cards with PINs will generated in batches up of to 50,000 (typically 5,000 ~ 10,000).
- A PIN consists of 8 digits (0 through 9).
- The PINs need to be unique, not only within the current batch, but amongst all PINs previously generated for cards with have not already been spent.
- Database of non-spent cards will never exceed 500,000.
- Cards in the database may change from non-spent to spent while creating a new batch of cards.
- It is guaranteed that there will only ever been one instance attempting to generate PINs / add new cards at a time.
- Generate a list of candidate PINs which exceeds the required amount by some factor -- maybe 10% (PIN generator will not check for duplicates within the list).
- Start a database transaction.
- For each PIN in the list, the database will be queried to check to see if a non-spent card with the same PIN already exists in the database.
- If the PIN does not already exist, a card will be inserted into the database using the PIN just tested along with other card attributes.
- If the PIN does already exists, it will be discarded.
- Repeat until the required number of cards have been inserted into the database.
- Commit database transaction.
- If the number of candidate PINs (after elimination of non-unique PINs) is not sufficient, the operation will fail and the database transaction will be rolled-back.
Does this seem like a reasonable approach? Any suggestions for improvement?
It sounds reasonable. I'm presuming you are going to use a small percentage of the PINs, right. (Otherwise storing all the PINs and randomly generating an "index" of what is left would be reasonable.
At most, I will be using .5% of the possible PIN combinations.
Like you mentioned, I was also considering keeping an inventory of generated PINs, and taking from that inventory as required to fulfill the requests for card creation.
Periodically the inventory level would be checked, and if required, additional PINs would be generated and added to the inventory to keep it stocked for the next use. This has the advantage of eliminating the risk of a failure when creating a batch of cards due to excessive collisions of PINs with those already provisioned in the database. It would also improve response time for processing the request -- possibly bringing it down to just a few seconds.
Thanks for your input.
subject: Insert Batches of Scratch Cards with Unique PINs into a Database