"Brian K. Woods" wrote:

>
> 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?

Yes. Do anything but place the Office ID as part of single string field with
Table ID and the same goes for generator value. Under no circumstances can this
provide a quick search, quick check or referential integrity.

Separate fields.

Eric has explained what he has already done in an existing system that he finds
works. It is more complicated than my suggestion, but it offers benefits. Make
sure you understand Eric's method before continuing.

Raymond.

> 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

--
Raymond Kennington
Programming Solutions
W2W Team B