🚀 go-pugleaf

Fun. :o)
With replication as each site is running the same program, with the same
database (most the time) you need to keep each database Unique key unique
when they are added togeather. We have gone down the route of creating a UID
for every record that is a combination of a number of values that make it
unique to the database.

Each database is given a 3 letter code that we store in a table (lets call
it config_table)
We also assign a 3 letter code to each table (lets have TableA, TableB,
TableC).
We also use two letters AA to ensure that if we ever need to seperate
further we can (its a same guard that will proberbly never get used).
We also a counter that goes from - to + 2000000000 (or what ever the maximum
is you can set)

So The UID for tableA, on database ABC, could be TBA   ABC
AA   -2000000000 (or TBAABCAA-2000000000 )
the next record would be TBAABCAA-1999999999
if we added a record to table B we would then have UID =
TBBABCAA-1999999998
The other database would no clash the UID's because the site code keeps them
different for example site DEF on tableA = TBADEFAA-2000000000

I hope this helps.

Steve


"Synergy" <fwliu@interchange.ubc.ca> wrote in message
news:3bc49371$1_1@dnews...
> Hi,
>
> I have recently finished an application using BCB4 and IB 6.  I have a
> difficult time planning how to provide updates to the databases that I
would
> eventually distribute. Sorry this may be long to read ... but I really
hope
> someone with a thorough understanding of the problem can help.
>
> I have a material database with a table for material, a table for
> material_class, and a table called material_class_link that gives the
> many-to-many relationship between material and material class by storing
the
> primary keys for each table.
>
> Now, the primary key for a newly inserted material or material class would
> have the value of the material and material class generator plus 1
> respectively.  Therefore, the same material classes inserted in different
> orders will end up with different primary key schemes.  This is what will
> happen once different users start to use their own copy of our software.
>
> We are required to provide an update for any new materials that we added.
> Please note that we are not looking for total synchronization for all the
> distributed material databases, since different users may want to keep
> certain records to themselves.
>
> The first problem is that with any replicator, straight replication of the
> material record that we wish to pass over will result in a conflict with
the
> user's primary key scheme.  The more tricky problem is that even if we
find
> a way to copy over a material record, the material class keys that we copy
> over will not likely retrieve the right material classes for the material
in
> someone else's database.  Worse yet, material class is only one such
> many-to-many relationship that we have to deal with!
>
> I am quite new to database programming and I really want to learn what is
an
> expert's solution to this problem. Please ask for any clarification that
you
> may need to further understand my problem.
>
> Thank you,
>
> Frank Liu
>
> (Below is my propose solution, a big problem though: this is a time
> consuming process to build a large update)
>
file://---------------------------------------------------------------------
-----
> -----------
> 1)     Use an utility to extract the insert statement from every table
> associated with a             material record that we wish to pass onto
> everyone.
> 2)     Search the distributed databases, using the material name, for the
> material record.          If they don't have it, generate a new material
> primary key from their generator,             insert the new material
> record.
> 3)     Search their databases for the material class records that need to
be
> associated             with the material record that we are passing on.
If
> they have the material                     classes, record the key values,
> else insert the necessary material classes then                   record
the
> key values.  Use these key values and the material key in an insert
> statement to update the material_class link table.
> 4)    Repeat 3) for all the many-to-many relationships that are associated
> with the                 material.
> 5)    Graphics blobs has to be inserted separately because the extract
> insert
>        statements done in 1) doesn't take care of them.
>
> With all this work ... I think I might just as well ask the users to type
> the record directly into their database.
>
>