--- In IBObjects@yahoogroups.com, "Brian K. Woods" <brian@e...>
wrote:

> I'm planning a project that would be well served by your type

setup (local

> dbs with a central master db).
> Might I be so bold as to inquire how you worked out the keys? A

straight

> generator won't work in this situation since
> each local db would have generators and would thus be generating

duplicate

> keys. I found an article on the

Brian,

I have a similar system. It is a distributed system with each user
running a local db and head office is running a master db, in
addition (to make it extra complicated!), all network data goes thru
our server where we store all transmissions and upload all data to
the local and master dbs when they 'login'. So, for an example, head
office may get a sales lead and want to send it to John Doe in
Iceland, so they create the sales lead on their db (with their own
local key), and when they talk to our server, the sales lead is
downloaded to us and we create an entry in our tables and create
a 'mapping' key. So if the local key (at head office) is 1111, we
will create key (say) 2222 and store this key-mapping in a separate
table. When John Doe dials-in to collect his data, we will send up
the sales lead data and John Doe's system will return to us the
local key used in the insert (say) 3333.
So now we have 1111 head office local key, 2222 server master key,
and 3333 John Doe local key all mapped and stored in our server
tables.
Now it gets interesting... if John Doe modifies that sales lead,
the head office wants to also get the data. So when John Doe dials
in, he sends down the sales lead changed data with key 3333 to our
server... based on our tables we know that that data is our 2222
key, so we update the 2222 key data in our tables. When head office
dials in, we take this changed sales lead data and map the 2222 key
to the head office local key of 1111. When the head office system
gets the data, the key is already 1111, so the local system knows
that the newly uploaded data is an UPDATE, not an INSERT so the
correct sales lead record is updated.

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.

So the key-mapping table is the following:
PKEY integer (primary key - created by generators - used
just to create uniqueness)
LOCALKEY integer (our local map of the remote key)
REMOTEKEY integer (local key of John Doe's remote system)
NETWORKKEY integer (who owns this remote key)

So when John Doe dials-in, part of the XML that is sent down to
our server also contains their unique NETWORKKEY so that we know who
they are and what local key they are using for that sales lead.

John Doe can also ask for a 'total synch', in which case our
server uploads all data (en masse), and after each data packet, John
Doe's system sends us back a <KEY>XXXX</> XML tag in the return
packet, so that our server can create a fresh set of local key
mappings for John.

We also have a timestamp table so that our server will only send
data to John Doe that has been changed since the last time he dialed-
in. (That's a whole other story!!!)

What complicates the whole business is that the sales lead data
may contain other foreign keys which also need to be mapped. So if
the sales lead has 20 fields, 5 may be other keys which need to be
mapped. So, when the sales lead is uploaded to John Doe's system,
the return data packet contains 5 XML tag structures like:
<LOCALKEY><KEY>9999</><FIELD>1</></>
so that our server knows that this local key 9999 is from field 1
of this sales lead which happens to be the primary key. Since we
know which fields are keys (since everyone uses the same metadata),
we can properly map each of the 5 key values in that sales lead. So
if John Doe needs to receive updates from that same sales lead next
day, we can properly map all the foreign keys from all 5 fields into
the XML that we send up to him.

Anyway, I hope this helps your design. Eric.