How to generate Unique coupen code?

Question

I am trying to generate coupen codes to provide discounts. I have tried with Math.Random() but I am not sure if it will assure an unique code each time ?.

Also I have tried with dbms_random.string utility of `Oracle' which is generating random numbers but it also does not guarantee the unique code.

Does anyone knows a good algorithm to do this ?

I am using java and oracle to develope the code.

EDIT: After looking few responses I would like to add that I have to store each and every generated code in the table. And the generated code should be Alphanumeric.

Answer 1

You could use the Java UUID class. It generates random 128-bit alphanumeric strings. The possibility of repetition of a string is astronomically low.

Specifically:

import java.util.UUID
.
.
String uniqueString = UUID.randomUUID().toString()
.
.

Answer 2

Random is random, but you correctly identified this doesn't mean unique.

You will need to store somewhere (like back in your database) the codes that have been used, and then scan the records when you create a new code.

Realistically, you might want to generate a 100 (or a 1000, or 10,000) coupons up front, store them, and assign them out as required.

Answer 3

I would combine random number + sequence number. That gives you a random number that is unique because the sequence number is unique.

Otherwise you need to do a trial-and-error approach. Maybe in advance to save run-time. Suppose you have a table "coupon-keys" with a unique index coupon_id. You generate the random key and then insert it. You catch the insert failure if not unique and retry until you have enough coupon keys generated. Additionally you have a column "used" and each time you give out a coupon you update the column to keep track of what coupon numbers are still available.

Answer 4

Create an array of alphanumeric characters that represent all valid characters for your coupon codes. Let's say it's all upper and lower-cased alpha characters.

String chars[]="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ".toCharArray();

You can convert any integer to a shorter code, basically by performing a number base conversion (albeit in reverse order).

int max=100000000;
int random=(int) (Math.random()*max);
StringBuffer sb=new StringBuffer();
while (random>0) {
    sb.append(chars[random % chars.length]);
    random /= chars.length;
}
String couponCode=sb.toString();

So, generate a random number, shorten it to a string, and insert it into your database. If the insert fails due to collision, then retry. Collisions should be fairly rare, and would only have a minimal cost when they occur. Your coupon codes should remain short and easy to key in.

Answer 5

Oracle from 10g up has the dbms_crypto package included to allow the generation of genuinely random sequences.

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm#i1000605 .

Having obtained some random RAW bytes, these can then be encoded into an suitable alpha-numeric string using the utl_encode package.

http://psoug.org/reference/utl_encode.html

If coupon codes are long enough, collisions should be infeasible, but you could still wrap the coupon creation in a PL/SQL function to handle any collisions. Eg. use a UNIQUE constraint and catch the exception if the INSERT fails. (As Bob Jarvis suggested.)

Answer 6

Here's my suggestions:

Your table should look something like

CREATE TABLE COUPONS
  (COUPON_CODE NUMBER
     CONSTRAINT PK_COUPONS
       PRIMARY KEY
       USING INDEX);        -- plus whatever other fields you need

Your code to generate unique codes should be something like

DECLARE
  nCoupon_code    NUMBER;
  bCode_unique     BOOLEAN;
BEGIN
  bCode_unique := FALSE;

  WHILE bCode_unique = FALSE LOOP
    BEGIN
      nCoupon_code := GENERATE_COUPON_CODE;  -- function to generate a coupon code

      INSERT INTO COUPONS (COUPON_CODE)
        VALUES (nCoupon_code);

      bCode_unique := TRUE;
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        NULL;  -- Fall through, loop back to the top, generate new code, continue
    END;
  END LOOP;  -- bCode_unique
END;

What goes on here is that the program first set the 'code unique' flag to FALSE, indicating that a unique code has not yet been generated. A loop is then entered, and will continue as long as the 'code unique' flag is still FALSE. A coupon code is then generated and stored into the database. If the code is unique all will be well, the 'code unique' flag will be set, and the loop will be exited. HOWEVER, if the code is NOT unique the INSERT statement will fail with a DUP_VAL_ON_INDEX exception. This will happen because COUPON_CODE is the primary key on the COUPONS table, and we know that a primary key has three attributes: it must be non-NULL, if must never change, and it MUST BE UNIQUE. (For our purposes here we could have used a UNIQUE constraint on COUPONS.COUPON_CODE and gotten the same effect). If the DUP_VAL_ON_INDEX exception is thrown because the coupon code generated is not unique the exception handler will be entered where the code will do absolutely nothing; that is, the 'code unique' flag will remain FALSE, the code will drop out of the exception handler, and because the 'code unique' flag is still FALSE the loop will start over again where another code will be generated, and etc, etc, etc, until a unique code is finally generated and the INSERT succeeds.

This may seem like a lot of work, but if the coupon code generation algorithm is chosen well it won't generate a lot of collisions and thus won't have to loop too often. IMO this is a reasonable solution considering the environment - in PL/SQL you count on using the database to do some of the heavy lifting, like guaranteeing uniqueness. You can spend a ton of time trying to come up with the One True Code Generation Algorithm Which Will Never, Ever Possibly Generate A Duplicate, or you can just come up with something that's good-if-not-perfect and work with the database to make sure that the codes which are finally chosen end up unique. YMMV. And implementing the One True Algorithm can be a lot of fun, if not a particularly good use of time. :-)

Share and enjoy.