Gift vouchers

[Janaka]

No this isn't spam!

I'm currently developing a gift voucher system for an ecommerce retailer.
The vouchers may be emailed to customers or printed and sent along with
postage. Either way the administration for creating vouchers will be
through an admin web UI.
Now I was thinking of using a guid in SQL Server for the voucher number
since it will be unique and hard for people to guess a voucher number.
However since these may be printed and sent they may not want to make the
customer type out something like c70c012d-4b14-4eca-bf4b-c484fbaa069c

Is there an easy way of making a unique 9 or 12 character long voucher
number such as h72j3i9c0l1j and still ensure that it will not be a duplicate
number?

Thanks J

 

[Lucas Tam]

Is there an easy way of making a unique 9 or 12 character long voucher
number such as h72j3i9c0l1j and still ensure that it will not be a
duplicate number?

I don't have an unique generation algorithm for you... but You can always
add a unique constraint on the column in the database, this will guarantee
any data being inserted will be unique. If the data is not unique, an
exception will be thrown (and then you can regenerate the value).

 

[Janaka]

That's true. I was going to implement that constraint on the db. My only
concern is that after making say 10 000 voucher numbers I'd have to
repeatedly do try catches around db inserts until it works. Sounds to me
like it could possibly hang the page or lock the table?

 

[Mark Harris]

You could make a function to generate a voucher and then add it to the
database. a 12 character/digit string will not be too hard to make unique,
the chances of re-creating a voucher would not be likely

That's true. I was going to implement that constraint on the db. My only
concern is that after making say 10 000 voucher numbers I'd have to
repeatedly do try catches around db inserts until it works. Sounds to me
like it could possibly hang the page or lock the table?

 

[Lucas Tam]

My only
concern is that after making say 10 000 voucher numbers I'd have to
repeatedly do try catches around db inserts until it works.

Well you should have an algorithm which gives you unique values... but the
constraint just adds an extra check incase your algorithm is bad.

 

[Anon-E-Moose]

My only
concern is that after making say 10 000 voucher numbers I'd have to
repeatedly do try catches around db inserts until it works.

Nah... 9^36 (assuming A-Z and 0-9) = 101,559,956,668,416. So the odds of
getting a duplicate are quite low.

If you add case sensitivity, that's even more combinations.

 

[Guest]

Dear Janaka,

In what language are you doing the coding.. Eg : In VFP you can use
SYS(2015).Im sure that there must be same kind of functions in VB etc too

Roshan Jayalath

That's true. I was going to implement that constraint on the db. My only
concern is that after making say 10 000 voucher numbers I'd have to
repeatedly do try catches around db inserts until it works. Sounds to me
like it could possibly hang the page or lock the table?