[Phpgroupware-developers] addbook conduit logic

[Brian Johnson]

I've been thinking about this a bit and think I've come up with logic to
manage the sync between the addbook module and the Palm addressbook - I'd
like to here people's opinion on possible logic problems that I may have
overlooked

Just to remind people, the addbook module stores the individual contacts in
a different table than the organization contacts so that more than one
individual can be associated with the same organization.  The Palm on the
other hand, has one table and one record per individual that includes the
organizations contact info, so if there are three individuals from the same
company, you have to add (or edit if changes) three sets of company
information

So here we go - I've tried to explain things well, but you might have to
read this carefully a couple of times to understand what I'm trying to say:

The phpgw tables include a table for the individuals and a table for the
organizations and a table called relations that ties the two together (I'll
also make some sugestions on how to simplify the database tables in a
separate email).  It also has a table called meta that lists all of the
records ids from the combined individuals and contacts tables (as well as
some record creation/modification time stamps, etc).  Please see my previous
post (yesterday) for what I believe is the relationship diagram used by
Heiko

I think that I need two new table to match up the phpgw info to the Palm
records.

The first table (let's call it palm_addbook) would contain a sequential
record id (id), a field for the palm record number (palm_rec_id), a field
for the phpgw user acount so that it will work in a multi-user system
(phpgw_account), a field to point to the phpgw addbook individual record id
that matches (phpgw_ind_rec_id), and a field to point to the phpgw addbook
organization record (phpgw_org_rec_id)

This handles the initial match up, but then I wanted any org info changes
from any one of the individuals for that org from the palm to make the
change to the org info in the addbook module (essentially change one set of
org info on the Palm and next sync, all org info for all individuals from
that org will be modified).  But how do I prevent an org info change from
individual one from being reset by the org info from individual two?  So I
add another table that fits between palm_addbook (call it palm_addbook2)
that contains a sequential record number (id), a field to point to the phpgw
addbook organization record (phpgw_org_rec_id), and field with a flag that
can be triggered when the org info is changed during a sync so that no other
record changes that organization's info again during the same sync (it would
be reset at the end of the sync).  One potential problem with this is that
an interupted sync would prevent the organization's info from being changed
during the next sync and any changes not transferred before th interuption
would be lost

Next problem - multiple individuals at an organization and one leaves to
join another organization.  Since I want the normal sync to transfer changes
to the organization info, this would potentially change all the
organization's info for all of the individual's linked to the first
organization.  Here is what I think may work.  Although organizations may
change their name or location, not many change both at the same time, so use
that as a trigger to link that individual to a new organization - but how do
I check to see if the new organization already exists or if a new one should
be created?  To check for an existing organization, the name and address
would have to be an exact match or a new one would be created

I think that's the best I can come up with - I'd like to hear suggestions
for a simpler system, improvements to this system, or even holes in logic I
haven't seen yet