Path: utzoo!utgpu!jarvis.csri.toronto.edu!cs.utexas.edu!usc!zaphod.mps.ohio-state.edu!uakari.primate.wisc.edu!aplcen!haven!decuac!shlump.nac.dec.com!ryn.esg.dec.com!uninet!beagle!godfrind
From: godfrind@ricard.enet.dec.com (Albert Godfrind)
Newsgroups: comp.databases
Subject: Re: Unique code numbers
Message-ID: <123@uninet.vbo.dec.com>
Date: 17 Jan 90 12:24:20 GMT
Sender: news@uninet.vbo.dec.com
Organization: Digital Equipment Corporation, Valbonne, France
Lines: 47


In article <1990Jan16.081519.279@cs.eur.nl>, reino@cs.eur.nl (Reino de Boer) 
writes...

>The problem is solvable by introducing a new relation for each ID:
>IDconfig = (lastID)
>and generating the new unique ID by something along
>ID <- retrieve( lastID )
>update( lastID )
>store( lastID )

Your solution is correct. One problem however is that the number-generating
relation is likely to become a 'hot-spot' in your database as many users will
be constantly updating it.

Consider the following variation : instead of reading and updating the lastID
each time you need a new number, do it only once every 10 or 50 or 100 IDs 
(depending on your needs); this has the effect of effectively pre-allocating
a range of 10 or 50 or 100 numbers for a given user. 

When a user has exhausted its reserve of say, 50 numbers, he just allocates 50  
more. Your IDconfig relation now really contains the 'high water mark' of
IDnumbers.

You will get something like :

-user 1 : retrieves lastID (value n) and updates it as n+50
-user 2 : retrieves lastID (value n+50) and updates it as n+100
-user 1 : stores new parts, with partIDs n+1, ... n+50
-user 2 : stores new parts, with partIDs n+51, ... n+100      <------------+
-user 1 : retrieves lastID (value n+100) and updates it as n+150           !
-user 1 : stores new parts, with partIDs n+101 ... n+150                   !
..                                                                        !
                                                                           !
Of course, one problem is that, should something go wrong (your system     !
crashes), you will lose range of numbers : for example, if your system crashes
while user 2 is storing part with partID n+60, then next time your application
restarts, it will start using partIDs starting with n+101 ... so you will have
a range or partIDs (60 to 100) unused. This may (or not) be acceptable by
your application.

+-----------------------------------------------------------------------------+
| Albert Godfrind                 These are MY views, all MINE !              |
| Digital Equipment Corp.                                                     |
| European Technical Center       INET: godfrind@ricard.enet.dec.com          |
| Valbonne, France                UUCP: ...!decwrl!ricard.dec.com!godfrind    |
+-----------------------------------------------------------------------------+