Re: surrogate keys and replication.

Sad,

> Now I solve the GUID problem, with one sequence of IDs on the main server.
> The clients ask the server to lease some IDs via special
> (application-layer) protocol. Server remembers who and when and what IDs
> have took.
> (in terms of segments [a..b],[c..d]... etc)

Sorry for long delay on this -- I've been out of town.

I don't know what the limitations of your client-side technology are.
However, I do have to speak against the approach taken by many "GUID"
programs -- to assign a unique numberical key, possibly using allocations or
random numbers, which contains no other information.

When you're looking to replicate or exchange data, there are 4 pieces of
extended "meta-data" you need to have for each row:

1) A row key
2) the table to which the row belongs
3) the server on which the "live" copy of the table currently resides,
i.e. the server which currently "owns" the row
4) an absolute timestamp of when the row was last changed

In situations like this (1) usually does have to be some form of numeric
(surrogate) key unless you'll take the trouble to use hashes, just for code
simplicity. But you want the other pieces of information clearly in the GUID
key; otherwise you need to do a lot of calculation and querying to figure
out, when Server 11 wants to update Row 283432 of Table "status", whether it
can be done locally or needs to be "exchanged".

--
Josh Berkus
Aglio Database Solutions
San Francisco