Subject Re: Was: Guid Support
Author Ian A. Newby
Hi Christian,
> We need as part of the key
> - an identifier for the database
> With this part, no duplicate key could be calculated between
> different databases
> - a high id, taken from the database
> (see
> This is like an application session number.
> With this part, a duplicate key in a certain database could
> not be calculated.
> - a low id, generated from the application
> This part is for speeding up the generation of keys (database
> access is only necessary when the low id goes over its
> maximum number, then I have to fetch a new high id)
> - a time stamp for handling multiple high ids
> A duplicated high id could occur when the id is overflowing or
> when we would set up a new database while not initialising
> the high id

How are you going to assign the database identifier? We do a similar
thing in one of our systems. The primary key is made up of a 3 char
database prefix, 2 chars for the table and 10 chars for a unique
number (from a generator). This gives us a char(15) key.

We are having problems managing this as people are not always
setting the database prefix properly before using the database!

Also a guid can I believe be stored as a char(8) which is smaller.
What would be needed is a guid to string udf to convert a char(8)
guid to a char 32 human readable.

> We came up with 71 bits for the key. So the important question:
> How do I encode this information to get ideal performance for any
> database (now just focusing on interbase, but the schema and key
> generating mechanism should be vendor independant).

You seem to be making a very complex system for key generation when
guids (or uuids) are available in most operating systems and many
databases (MSSQL server, Oracle etc)

Having tried both approaches, I would use a guid. These can also be
generated in the application rather than the database if desired,
requiring no access to the database (unlike your method which
requires the high id to be generated via the database).

Ian Newby