🚀 go-pugleaf

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)
//--------------------------------------------------------------------------
-----------
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.