At 02:08 PM 02-12-01 +0000, you wrote:

>Hi,
>
>I'm working on a database which now, will be used in several places,
>but not as a central database, but as a seperate database at every
>place. Within a few years, it is quiet possible that the seperate
>databases will merge into 1 (thus a central database). I use a lot of
>Autogenerated unique keys, but when the seperate databases will
>merge, the key will not be unique anymore... how can I avoid this?
>How can I design this database so it will be easy to merge into 1 at
>a later stage, but with the uses of an autogenerated key (thus a
>primary key of 1 field?)

Unless you make provision for globally unique primary keys now, your only option for later merging would be some kind of semi-manual exercise where you would replace the current primary keys database-by-database in a predetermined sequence, propagating the new keys into the columns of dependent tables.

An obvious one would be to use GUIDs from the outset, so that all keys would be "world-unique".

Another would be to add a second int64 column to all primary keys now and populate it with zero through a trigger. At merging time, apply a generator to all of the records as they are processed into the consolidated database. That would reduce the amount of work at the merging stage but it would still require careful work to propagate to dependents.

Another way would be to allocate and enforce ranges of generator values to each of your current branches, thus ensuring world-uniqueness with a minimum of fuss.

Whatever you do to plan for the merge will need careful management at one or more points in history...

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________