At 09:08 AM 11/02/2004 +0000, you wrote:

>Hi, all.
>
>the dbs of my costumer are organized in a
>Hub-and-Spoke scenario. The databases will
>all be replicated among each other.
>
>Because of records of the same table can be created in every
>database it is necessary to have a unique primary key for the
>records. My idea is to create a compound PK which consists
>of an ID which is specific to the database in which the record
>is created and a value created by a generator.
>
>My questions:
>1) Is this a good idea or causes it problems?

It's OK.

>2) Is it better to store the compound PK in a VARCHAR column (e.g.
>"1_45" with db ID = 1 and 45 generated by a generator) or to use two
>INTEGER columns and define the primary key as the combination of the
>two columns?

Personal choice (but see below). But don't depend on the varchar to give
you a correct numerical ordering of creation sequence - it shouldn't
matter, since if creation order is important then by atomicity rules you
shouldn't depend on the primary key for it.

>- How will the use of a VARCHAR column as PK in my tables affect the
>speed of my queries?

Any index with a lot of duplicated sequences will tend to get slower as it
gets larger, though Firebird is pretty good about bitmapping the dup
sequences and getting to the unique sequences. At least with a compound
integer key you can define the PK across (Generated_number + Site_ID) in
that order, to avoid that degrading tendency.

Have you considered using a single-column GUID? Totally random and unique.

You might like to read Dalton Calford's papers on the topic of coodinating
keys across database boundaries:

http://firebirdsql.org/index.php?op=useful&id=calford_1

/hb