Thanks, guys, for the responses - to minimize mailings, I'll respond to a
couple of you inline...

> -----Original Message-----
>From: Raymond Kennington
>Have you considered using a unique "Office ID" field as well as a

generator?

Raymond, yours and some of the other responses make me realize I hid the
forest with the trees.
My question was not how to get a unique key - I could use a 1x char id where
say the first x chars
are the office id and the next x chars are a table id and the last 11 chars
are the integer-to-string
representation of a generator value. My question was two-fold:
Is there a better way to do it?
and
If I use such a non-integer key, am I going to run into trouble down the
road with ibobjects - for example,
in situations where I link two queries master-detail or use lookup links
with a lookup combobox?

> -----Original Message-----
>From: Alan McDonald
>I do this by spacing my generators - that's in fact the only difference
>between the replicated db's, the generator for tables in dbmaster start at

0

>, the generator in db2 starts at 500,000,000 an so on,
>If course this requires that insert numbers are not going to ovetake you

and

>that the number of DB's is within range. The next option to to make a
>composite PK, wher ethe genrators are all the same, but the tables jn DB
>have a db field and generator field which combine to make the pk.

Alan, the first option scares me - the same type assumptions about value
ranges for the year portion of dates lead to the infamous Y2K scare. I
know -2 billion to +2 billion is a lot of distinct values, but I'd still
rather not rely on an unenforcible restriction/assumption on value ranges.
As for the second option, the only problem I can see with that is that there
would be a low distribution of values on the db field part of the key -
making the index for it extremely inefficient ( if I understand such things
correctly - I'm still relatively new to firebird, but database theory is
universal... )

> -----Original Message-----
> From: Eric Handbury <ehandbury@...>
> The way I designed it was it leave all key-mapping logic at our
> server, so that the remote systems (John Doe and head office) only
> know of their own local keys... they isolated from all that mapping
> stuff.
> Every remote system (John Doe) has a unique network key so that
> the server knows what remote system owns that local key. Because
> more than 1 remote system may get that sales lead and our server
> must map all remotes keys to the same local key.
> Anyway, I hope this helps your design.

Very interesting, Eric - an angle that makes sense but that I hadn't thought
of.
It allows you to keep the simple integer key values but still have unique
keys across
all levels. Nice - but a little more work than I want to implement (if I can
avoid it) with all those mapping tables on the server. I'd prefer to keep
the key system simpler than that.

> -----Original Message----->
>From: Marco Menardi
>I've an algorithm that translates a numerical value to a string, so in
>a small space you have plenty of unique values. This is not fine
>tuned, and maybe some value is "lost" (it's basicalli a base 10 to
>base 36 translation, but I'm not so good in mathematics).
>Consider that the number 60.466.175 becomes ZZZZZ if base 36 is used!
>here the code, free for use and open to improvements (that, please, I
>would like to have back :)

Thanks for the code! Actually, I have my own similar algorithm in pascal,
but its
purpose is opposite - convert a string to more economical integer or double
value for storage
and lightning fast sorting.
As it applies to this situation, I could use it to turn my
11 character string-ized integer portion of the key into a 4-6 character
alpha - would save a lot
of storage space in a large database. Also, if one had a short char field
that you always searched as a whole,
i.e. no LIKE '%X%' type searches needed, then I bet the index lookup and
sorting would be much faster. In
fact, I think I've seen somewhere that Firebird already uses a similar
encoding technique, which is why there
are limitations on using indexes with LIKE '%X%' style clauses. So it
probably wouldn't be useful, since the indexes already
do it internally, but it could still be fun to play with doing it in stored
procedure code - *geesh, I need a life!*

Perusing your code, I think ( if I remember my implementation )
that it could be tweaked a little. However, yours may be optimal for a
stored procedure implementation.
I'll take a closer look at yours as well as play with converting my two-way
algorithm to stored procedure - just for fun.
If it works out and seems useful, I'll post it back for you to look at.

Thanks all,
Brian