aspose file tools*
The moose likes JDBC and the fly likes Insert Batches of Scratch Cards with Unique PINs into a Database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Insert Batches of Scratch Cards with Unique PINs into a Database" Watch "Insert Batches of Scratch Cards with Unique PINs into a Database" New topic
Author

Insert Batches of Scratch Cards with Unique PINs into a Database

Ron McLeod
Ranch Hand

Joined: Feb 12, 2013
Posts: 335
    
    7

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.

Requirements:
  - 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.


Strategy:
  - 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?

Thanks.


Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31075
    
232

Ron,
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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Ron McLeod
Ranch Hand

Joined: Feb 12, 2013
Posts: 335
    
    7

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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Insert Batches of Scratch Cards with Unique PINs into a Database