Link to home
Start Free TrialLog in
Avatar of kenuk110
kenuk110

asked on

Auto create unique ID for a database entry

Hi,

I have a table which contains references to blocks within supermarkets. The code for these blocks uses a type of telephone type code:

44-01-006-AA11-S

This code = United Kingdom, London, Store 006, S = Supermarket and AA11 is the block code.

These codes are printed on forms so the person reading it can understand 'in English' where this block is located. I should mention that my term 'block' refers to a display in a supermarket, the cereal block, the coffee block, the shampoo block etc.

What I need to try to do though is also have a second code for this block, something that is shorter than xxx-xx-xxx-X-xxxx. Our merchandisers are going to start takin gpictures of these blocks on a 'PDA' and in order for us to initially identify the block is by typing in the full code, they take a picture, name it then send it to our server to break the code down again. My problem is that 1 - they have around 40 blocks to enter per 6 stores they visit each day and 2 - they will type the code wrong, I just know it! If the code doesn't come in to the system properly then our report will not be able to find the picture.

The most blocks we will ever have in one country is 99,999. is it possibe to create an automatically created unique 5 digit alphanumeric code that is just added when we add the main code for this block in our database or am I thinking way off the mark here?

So this:
044-01-006-S-AA95
would equal
BY32S (as an example)

Any help would be much appreciated.

Best Regards,

Ken

Avatar of gena17
gena17

Option 1: Use GPS on the PDA (if possible) to recognize the country, the city, and maybe more.
Option 2: Generate a random 5-chars code for each block in advance and store it in your database. So your users will use these short codes. The chance for mistake will be small since there are 36^5 available options and you will only need 10^5.

Hope this helps
Avatar of kenuk110

ASKER

Thanks for the suggestions!

I'd like to go with option two but how do I generate this amount of codes randomly? Is there a way to do it in SQL or do I need to look for another program OR do I have to sit and think of 99,999 differnent codes from my own head?

Cheers,

Ken
ASKER CERTIFIED SOLUTION
Avatar of gena17
gena17

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial