[Phpgroupware-developers] contacts and CK-ERP (was CK-ERP v.0.8.1 released)

[Dave Hall]

Hi CK,

I have moved this to the dev list.  Our users don't really need to know
all the technical details of how it all works :)

I know this thread has died a little, but I have been busy.

On Sat, 2004-07-31 at 16:14, C K Wu wrote:
> Brian Johnson wrote:
> 
> >C K Wu (address@hidden) wrote:
> >  
> >
> >>Hi, Brian,
> >>
> >>    
> >>
> >>>You know I like ck-ledger, but the inability to still not use the phpgw
> >>>addressbook table directly causes me concern.  I know that any parallel
> >>>contact storage system that relies on a sync mechanism is bound to 
> >>>encounter
> >>>occasional problems.
> >>>
> >>>Perhaps it is due to my misunderstanding of how the "systemwide unique id"
> >>>works
> >>>
> >>>
> >>Systemwide unique id means unique across all transactions.  Say, a newly
> >>inserted customer
> >>record gets id 123, the next inserted invoice gets id 124, the next
> >>timesheet gets id 125,
> >>so on and so forth.  As far as I can tell, addressbook only maintains id
> >>uniqueness within
> >>individual type of transaction, ie no organisation record could have
> >>duplicate id, but a person
> >>record can have the same id as an organization record.
> >>    
> >>
> >
> >Persons and organizations do not overlap id numbers in the current
> >addressbook.  They are both listed in the central table phpgw_contact and
> >contact_id is a primary field with an index whick prevents a person from
> >sharing the same id as anorganization.
> >
> >However, that is still only uniquieness within that app, not across multiple
> >apps .. so still may not provide what you want.
> >  
> >
> You are absolutely right.

It would be possible to include a linking table within CK-ERP.  This
would provide the unique id within CK-ERP.  I have not looked at the db
design for CK-ERP, so maybe I am missing something.  I would have had a
look at CVS, but CVS is not available for either ck-ledger or ck-erp on
sf.net and ck-[erp|ledger].sf.net are both down.

> 
> >How do you do it?  Search multiple tables to ensure an id is not reused?  Or
> >maintain a record somewhere that contains the next id to be used (wouldn't
> >this system create a performance bottleneck?)?
> >
> >
> With Postgresql, all id is assigned from a single sequence.
> With MySQL, all id is assigned from one single auto-increment column.
> 
> The design is simple and efficient.  However, it requires all 
> id-assigning scripts to abide by the rule.
> Any single script that does not conform to the rule will screw up the 
> design.  That's why I think
> it is very difficult to implement within phpgw.

Yes this is true, but any system, regardless of what the business rules
are, will fail if those rules are not observed.  

The auto increment feature is optional.  Where it is used, then the
database should do the job, not the script calling it.  Inserting a
value into an auto increment field is wrong.  If you find them, let you
know and we will fix it.


3rd Normal Form design principles recommend that all primary keys should
be integers, to increase performance and such PKs must be unique.  The
auto increment feature ensures such ids are unique, but there is no
requirements to use the feature.  If you don't use auto increments, then
it is up to the application logic to maintain such unique ids, which has
some overhead and can cause problems.

I agree with Brain and others that CK-ERP really should use the API
where the feature is already implemented.  This should reduce the amount
of work in maintaining CK-ERP.  As I have offered and done in the past,
I am willing to assist CK (and others) in getting CK-ERP working with as
much of the API that is practical.  I won't be writing code for CK's
project but will be willing to provide advice.  The amount of time I can
spend on this will vary, depending on my workload.  A public cvs would
make this a lot easier :)

Cheers

Dave

-- 
Dave Hall (aka skwashd)
API Coordinator
phpGroupWare