Auto generation of ID

Question

I need to generate an id with the following features:

1. Id must be unique

2. Id consist of two parts 'type' and 'auto incremented' number

3. 'type' is integer and value can be 1, 2 or 3

4. 'auto incremented' number starts with 10001 and incremented each time id is generated.

5. type is selected from a web form and auto incremented number is from the database.

Example: if type is selected 2 and auto incremented number is 10001

then the generated id is = 210001

There may be hundrads of users generating id. Now my question is, Can this be done without stored procedure so that there is no id confict.

I am using ASP.Net(C#), Oracle, NHibernate

Answer 1

As you use Oracle, you can use a Sequence for that.

Each time you call your_sequence.NEXTVAL, a unique number is returned.

Answer 2

If you can't use auto incrementing types such as sequences, have a table containing each type and keeping score of its current value. Be careful to control access to this table and use it to generate new numbers. It is likely it will be a hot spot in your db though.

Answer 3

Why isn't the NHibernate implementation of Hi-Lo acceptable?

What’s the Hi/Lo Algorithm

Answer 4

What's the point in having the first digit of the ID to define the type? You should use a separate column for this, and then just use a plain auto-incrementing primary key for the actual ID.

Answer 5

The cleanest way is - as Scott Anderson also said - to use two columns. Each attribute should be atomic, i.e. have only one meaning. With a multi-valued column you'll have to apply functions (substr) to reveal for example the type. Constraints will be harder to define. Nothing beats a simple "check (integer_type in (1,2,3))" or "check (id > 10000)".

As for defining your second attribute - let's call it "id" - the number starting from 10001, you have two good strategies:

1) use one sequence, start with 1, and for display use the expression "10000 + row_number() over (partition by integer_type order by id)", to let the users see the number they want.

2) use three sequences, one for each integer_type, and let them have a start with clause of 10001.

The reason why you should definitely use sequences, is scalability. If you don't use sequences, you'll have to store the current value in some table, and serialize access to that table. And that's not good in a multi user system. With sequences you can set the cache property to reduce almost all contention issues.

Hope this helps.

Regards, Rob.