Schema for storing download codes
Posted by: Zach Werner
Date: June 06, 2013 02:12PM

I am working on a project for a company that wants to provide unique download codes with their products. I am trying to decide on a format to use that will be unique and quick to insert into MySQL. The company will probably only create 100,000 codes a year but will need to insert as many as 10,000 at a time. The code will probably be 12 characters long with the last character being a check digit to avoid most false lookups. I have not settled on the storage engine but will likely use InnoDB because it supports transactions.

My initial idea was to use a completely random value (possibly using a stored function) as the code and make that the primary key but I am wondering if the inserts would be slow and problematic.

My second thought, and the idea I am currently leaning toward, would be to use a two part code that combines a primary key id and a random value as the download code. Basically I would generate a random string (not necessarily unique) and insert it into the database. The table would use a standard INT as the primary key. After I've inserted all the records (probably 500 at a time?) I could then select all the records back out and use the combination of the primary key and the random string as the code. I would think both inserts and look ups would be quicker this way since it would be based on the primary key. It would probably require a second index that references that batch insert so it could be read back out but this would be true regardless of the format.

Does either of these seem like a decent approach? The first is a little simpler but I’m worried it would be slow. Any thoughts or suggestions would be much appreciated. Thanks.

Options: ReplyQuote


Subject
Written By
Posted
Schema for storing download codes
June 06, 2013 02:12PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.