Reputation: 1994
I need to generate an id with the following features:
Id must be unique
Id consist of two parts 'type' and 'auto incremented' number
'type' is integer and value can be 1, 2 or 3
'auto incremented' number starts with 10001 and incremented each time id is generated.
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
Upvotes: 2
Views: 1005
Reputation: 17705
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.
Upvotes: 1
Reputation: 12523
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.
Upvotes: 1
Reputation: 33128
Why isn't the NHibernate implementation of Hi-Lo acceptable?
Upvotes: 1
Reputation: 74320
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.
Upvotes: 0
Reputation: 55624
As you use Oracle, you can use a Sequence for that.
Each time you call your_sequence.NEXTVAL
, a unique number is returned.
Upvotes: 3