How can I generate unique IDs for different items inserted into a table
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.