reino@cs.eur.nl (Reino de Boer) (01/16/90)
For the first time in my life I am planning a reasonably large database, and I have run across a (solvable) problem. A lot of examples in literature about relational databases involve the following kind of relationships: Supplier = (Name, Address, etc., Parts supplied) Part = (Name, Description, etc., Suppliers) which is then normalized to Supplier = (SupplierID, Name, Address, etc.) Part = (PartID, Name, Description, etc.) SP = (SupplierID, PartID) I was wondering if there is a relational DBMS that gives me the opportunity to indicate that: 1. SupplierIDs need to be unique 2. PartIDs need to be unique and, most important of all 3. Frees me from the burden to make up new SupplierIDs and PartIDs. 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 ) but I would like a more general mechanism. Perhaps a special field data type `unique id', that generates a new value when needed. Awaiting your replies, thanks already -- Reino -- Reino R. A. de Boer Erasmus University Rotterdam ( Informatica ) e-mail: reino@cs.eur.nl
itkin@mrspoc.Transact.COM (Steven M. List) (01/17/90)
reino@cs.eur.nl (Reino de Boer) writes: >I was wondering if there is a relational DBMS that gives me the >opportunity to indicate that: >1. SupplierIDs need to be unique >2. PartIDs need to be unique >and, most important of all >3. Frees me from the burden to make up new SupplierIDs and PartIDs. >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 ) >but I would like a more general mechanism. Perhaps a special field data >type `unique id', that generates a new value when needed. Unify (both 4.0 and the newer Unify 2000) permit you to specify that a field should take on a unique value. The number is automatically assigned by the system, so it frees you from having to take care of it. The only drawback to Unify's system is that the number is only unique as long as the record exists in the database, since they use the Row ID for the unique value, and Unify's Row IDs are guaranteed to be unique and to remain attached to the record as long as the record is in the database. -- +----------------------------------------------------------------------------+ : Steven List @ Transact Software, Inc. :^>~ : : Chairman, Unify User Group of Northern California : : {apple,coherent,limbo,mips,pyramid,ubvax}!itkin@guinan.Transact.COM : : Voice: (415) 961-6112 : +----------------------------------------------------------------------------+ -- +----------------------------------------------------------------------------+ : Steven List @ Transact Software, Inc. :^>~ : : Chairman, Unify User Group of Northern California : : {apple,coherent,limbo,mips,pyramid,ubvax}!itkin@guinan.Transact.COM :
jsc@sequent.UUCP (J. Scott Carr) (01/17/90)
In article <1990Jan16.081519.279@cs.eur.nl> reino@cs.eur.nl (Reino de Boer) writes: >I was wondering if there is a relational DBMS that gives me the >opportunity to indicate that: >1. SupplierIDs need to be unique >2. PartIDs need to be unique >and, most important of all >3. Frees me from the burden to make up new SupplierIDs and PartIDs. ORACLE Version 6.0 introduces a database object called a Sequence, which is used to generate unique identifiers. When inserting a new row, you select sequence.next, which provides the next unique ID. The object allow specification of starting value, increment, and whether the sequence should cycle. All in all a good extension. -------- Scott Carr uunet!sequent!jsc Sequent Computer Systems (503) 526-5940
michael@ddsw1.MCS.COM (Michael Duebner) (01/17/90)
In article <1990Jan16.081519.279@cs.eur.nl> reino@cs.eur.nl (Reino de Boer) writes: >For the first time in my life I am planning a reasonably large database, >and I have run across a (solvable) problem. > [...some lines deleted...] >Supplier = (SupplierID, Name, Address, etc.) >Part = (PartID, Name, Description, etc.) >SP = (SupplierID, PartID) > >I was wondering if there is a relational DBMS that gives me the >opportunity to indicate that: >1. SupplierIDs need to be unique >2. PartIDs need to be unique >and, most important of all >3. Frees me from the burden to make up new SupplierIDs and PartIDs. > >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 ) > >Reino R. A. de Boer >Erasmus University Rotterdam ( Informatica ) >e-mail: reino@cs.eur.nl Here is an example in FOCUS, a 4GL that is not quite a relational database, we seem to prefere the parent child, multi segment instances environment. You can assign numbers quite simply by describing the key field so that it is numeric and sorted high to low. This is required since in FOCUS there is no 'go to EOF', read the last and highest key used. However, it is possible to go to the beginning of the file, then take one step forward and read that key value. Add 1 to it and you have a unique keyvalue. Michael Duebner (michael@ddsw1.MCS.COM, <well-connected>!ddsw1!michael) Voice: (708) 566-8910 Macro Computer Solutions, Inc. "Quality Solutions at a Fair Price"
godfrind@ricard.enet.dec.com (Albert Godfrind) (01/17/90)
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 | +-----------------------------------------------------------------------------+
robf@squid.rtech.com (Robert Fair) (01/17/90)
>From: reino@cs.eur.nl (Reino de Boer) writes about a parts/supplier problem: >I was wondering if there is a relational DBMS that gives me the >opportunity to indicate that: >1. SupplierIDs need to be unique >2. PartIDs need to be unique >and, most important of all >3. Frees me from the burden to make up new SupplierIDs and PartIDs. One easy way of doing this is to use INGRES 6.3 "logical_key" datatypes which can be automatically set by the system to unique values: table_key - An 8-byte string unique within the table object_key - A 16-byte string unique within the entire DBMS installation. If the column is declared as 'system_maintained' then INGRES will places a unique value in the column when a new row is inserted, so providing an automatic unique value for the column. A typical usage might be: CREATE TABLE supplier ( supplierID object_key with system_maintained, ... ) CREATE TABLE supppart ( supplierID object_key not null not system_maintained, ... ) (suppart.supplierID is not system_maintained since it is derived from supplier.supplierID - you don't want different values here! To enforce this relationship one could create a Rule on suppart/supplier) Robert Fair Technical Support Ingres Corperation
bochner@speed.harvard.edu (Harry Bochner) (01/18/90)
In article <1990Jan16.081519.279@cs.eur.nl> reino@cs.eur.nl (Reino de Boer) writes: I was wondering if there is a relational DBMS that gives me the opportunity to indicate that: 1. SupplierIDs need to be unique 2. PartIDs need to be unique and, most important of all 3. Frees me from the burden to make up new SupplierIDs and PartIDs. INFORMIX's version of SQL has a datatype called 'serial' that does what's needed here: when you add a record, any field of type serial is automatically assigned a unique value. The one thing that's sometimes awkward with this approach is that the 4gl program doesn't know what unique serial key was assigned, and can only find out by selecting the record back from the table. That usually works alright, except that there may not be any other field that's guarranteed to be unique ... -- Harry Bochner bochner@endor.harvard.edu