How can I generate unique IDs for different items inserted into a table

obb-taurus used Ask the Experts™
I'm working on an inventory application and a requirement is that the system generate a unique stock number for each item.  The only hitch is that there are several categories and each category needs to follow it's own numbering sequence.

To give a very bad example of what I'm trying to accomplish, in essence it would be like creating a table for each category and having an identity in each of those tables, this way whenever a new item is inserted into a specific category the numeric sequence would be consistent eg. 1001, 1002, 1003 etc.

I'm just looking for a better way to achieve what I've outlined in my above example.

Hope this make sense.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015
Most developers I know will handle concatenated primary keys in the front-end and not in the database, as users will need to edit these values (CLOTHES_001, FRUIT_002, WEIGHTS_042, etc.)

In your front end write SQL (VB, whatever) to get the current Max number for your category (CLOTHES, FRUIT, WEIGHTS), then add one, and that's what the users will see as the unique id.

Then insert the key as a 'business key' with the rest of the record, where the table has an actual identity(1,1) field as the actual key for relating to other tables.

Hope this helps.


I see what you are saying, obviously the "business key" would be in a seperate field from the category and I would just concatenate them in my code for display or printing purposes.

Thanks for the help.
Another approach is: create a table of IDs:

category  next_id
FRUIT                1
CLOTHES          1

When you add a FRUIT, get it next_id for FRIUT, then increment it in the ID table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial