Subject Was: Guid Support
Author cl_corba_at
Hello everybody!

We are developing distributed databases over 4 hierarchies and want to
use a calculated key value.

We didn't want to take the GUID approach, because the GUID needs
128 bit. So we did a design for a possible calculation of the
key and came to the following solution:

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
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
This time stamp could be the amount of month from now on,
depends on the estimated overflow of high ids (probably years)

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).

- encode it in an string
Define an alphabet (A-Z, a-z, 0-9 = 62 different values) and
convert it to a 62-based number and store it as a string.
Ensure always the same length (= 12 chars).
What kind of string do I choose? I do not want multibyte strings,
I do not whant code page translation (Interbase and BDE),
should I put the most changing part in the front of the string
or the least changing part?

- store it in 3 integers
a collegue of mine favors this solution.
When I join between tables I have to join between 3 fields
always. We will do performance tests, he claims this
solution is faster then the one above.

- use a numeric(22, 0)
is this possible or is "numeric (18,0)" the top of the roof.
How do I store this values in my application code (delphi)?

Generell, I would be very interested in encoding techniques
for (primary) key values.
Thanks in advance for your comments.