[Chicago] auto incrementing - what options do we have?

[Lukasz Szybalski]

On Fri, Oct 10, 2008 at 10:43 AM, Kumar McMillan
<kumar.mcmillan at gmail.com> wrote:
> On Fri, Oct 10, 2008 at 8:53 AM, Lukasz Szybalski <szybalski at gmail.com> wrote:
>> Hello,
>> I was wondering if anybody has a good strategy for auto incrementing fields?
>>
>> I want to auto increment field called "case#" .
>>
>> I have a choice of database auto increment on field "case#" or do it
>> myself? (correct? No other choices exists? or something in between?)
>>
>> 1. I would like to be able to do pick a number where we will start
>> doing a case#?  (1,000,000+)
>> 2. Reserver a case# for a special group which can auto increment case#
>> between 2,000,000-2,999,999, and add them as they come.
>> 3. I don't want to use (system_id)
>>
>> So it seems as the only way is to make my primary key:
>> case# - unique key, primary, not auto incrementing and let some
>> program manage auto incrementing.
>>
>> What options have worked for you in this situation?
>
> Just make case_number a unique column.  Then all race conditions are
> handled by the database.  If you want control over it don't make it
> auto-incrementing, do that yourself.  case_number = last_case_number +
> 1.  As long as you do the select of the last case number *and* the new
> insert all in one transaction, the database should be smart enough to
> lock the right rows for you and all simultaneous selects of last case
> number will block until the current insert transaction has committed.
> Actually, you might have to explicitly declare table level locks as
> opposed to row level locks.  Most dbs support LOCK TABLES, i.e. for
> myql : http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html which
> is what you want in this case.
>

This is doable.... The range of case# is assign to 3rd party vendor so
I will let him worry about auto incrementing as long as he doesn't
send me duplicates.  I'll do the separate table and make sure the auto
incrementing and insert happens on the same transaction. Has anybody
experimented with that and potential problems?  I don't see another
approach so why would this be  bottleneck compared to regular db auto
increment with another unique case#?

"A quick and dirty trick would be to have a normal auto-increment
field and, after insert, compute "case" and update the insert."
A little change to this:
Is it possible possible/ allowed by a database to let the db
autoincrement the filed, and then change that autoincremented number
to what I need it to be?  (3 possible choices for db might be mysql,
postgresql, maybe mssql?)


> However, if your customers are saying they need to set ranges of case
> numbers for special groups then it sounds to me like a much larger
> problem: your users are putting special meaning on case numbers before
> the cases are created.  Why?  It smells to me like other databases are
> at work (like, *gasp* someone's personal Access database).  If that's
> what's happening then you are essentially working with the
> "distributed database" problem.  This is solved in a number of ways.
> The easiest is to kick everyone in the groin and make them to stop
> using Access because you just built a new db.  Another solution would
> be the use of a central server that assigns node IDs to each database
> (good luck integrating this into MS Access).  Then, all case numbers
> are unique by node ID + case ID.  I.E. node 1 + case number 1 vs. node
> 2 + case number 1 are two unique case numbers.  Another approach is to
> use hashes that uniquely identify each case by content somehow.  This
> is how distributed version control works (google for Mercurial or git)
> ; it does not rely on any central server at all.

This could be interesting.. I'll look it over to see how this
application could be running in a distributed way.

Lucas