Re: [Phpgroupware-developers] Database buglist (fwd)

[Michael Dean]

On Mon, 2003-04-28 at 16:03, Chris Weiss wrote:
> >Registration
> 
> this thing is hacked.  IIRC it's functionality has been replaced as part of 
> sitmgr.
>  Cant delete it cause people are still using it, but it's not worth dwelling 
> on
> unless you plan on maintaining it for some odd reason.

I was under the impression jengo coded this from scratch?

> 
> >
> >Last but not least please let me say that the whole database is badly
> >designed and to much work on the data is done in the API.
> >This will slow down the application!
> 
> keep in mind "the whole database" is collections of stand alone scripts 
> written by
> various people, some of whom were writting their very first SQL.  Also keep 
> in mind
> were dealing with a "lowest common denominator", not all DB's support forien 
> keys
> and limit and subselects and blobs and other such usefull things.

I've tried in the past to lend my expertise here.  I gave up when
milosch was implementing the addressbook schema in phpGW.  It has no
normalization and the searches for it hit nearly every column.

I think a big problem with some of the core apps is they are implemented
to "get them done" with the intent of going back and fixing design
issues later.  The refactoring involved in this is very expensive and
can have negative effects on dependent applications.  Of course, lots of
other software is developed the same way - commercial and Free alike.  

If you really want to see the design change, be willing to lend a hand
in analysis of impact, design, coding, testing, etc.  

> 
> >
> >Also other things that will slow down phpGW on the database level are
> >missing(!!) indexes on "foreign keys" and other often used coloumns!!!
> 
> you mean like the apps in active development who may have not even finalaized 
> a
> schema yet?  Why are you doing  this in HEAD and not .16 again?  I don't get 
> it.

Any column used in a foreign key type relationship for joins should
absolutely be part of an index.  It makes a huge difference.  The schema
proc doesn't create foreign keys, but it should be able to.  Even MySQL
could take advantage of them with the INNODB format.

> 
> >
> >Another bad thing are relations between tables based on (var)char coloumns -
> >this will also slow down the thing!
> 
> depends on the database.  MySql and MSSql are quite fast with indexed varchar
> relations, i'm not familiar with others.

Varchar fields themselves do no perform very well because of their
dynamic length.  Indexing and joining varchar fields creates unnecessary
overhead for resources (disk space and processing).  It is always a good
idea to assign your object a distinct ID and reference that object in
other tables by the ID.

cdb was one of the best examples in phpGW for design.  It wasn't totally
normalized (for performance reasons I'm sure), but was designed,
documented, and never implemented :(

But, overall, some things in phpGW need to change and should.  If core
can come to a consensus on final schema changes, it should just be
worked into a roadmap.  Maybe for a 2.0 version?  Assuming 1.0 ever gets
here ;-)

Mike
-- 
Double Choco Latte - http://dcl.sourceforge.net/
GNU Enterprise - http://www.gnuenterprise.org/