Re: Call for testing experimental patch

[David Tangye <..hidden..>]

On Thu, 2006-09-14 at 08:13 -0700, Chris Travers wrote:
> In ap there is a primary key called "id" which is guaranteed unique
> within the ap table.  It is generated from the 'id' sequence.  The ar
> table is similar.
> In gl there is a primary key called 'id' which is guraranteed unique
> within the gl table.  It is generated from the 'id' sequence.
> In the acc_trans there is a pseudo foreign key called trans_id which
> refers to any of (among other things) the id fields from ar, ap, and
> gl.
> 
> So if the id sequence gets out of whack, when you enter, say, a GL
> entry, it will prevent you from assigning an id to the entry if it
> exists in the gl table but not if it exists in the ar or ap tables.
> In the latter case the pseudo foreign key in acc_trans becomes
> ambigous.
> 
> Does this make more sense now?
Yes. acc_trans has an incomplete foreign key. Its missing a NOT NULL
acc_trans_type column, whose values must indicated the target of the
relationship, ie 'GL', 'AR' or 'AP' (add others that relate to your
'among other things' comment) to point to the relevant table. 
> 
> Long run, these tables are going to be radically altered to avoid this
> problem in a more structural way, but for the moment, this seems like
> the easiest way to stabilize the situation and prevent more corrupted
> data.
I had another quick look at the SL and LedgerSMB DDL files. I hate to go
there, because its so obvious to me that the schema is such utter crap
that its no wonder the system has bugs.

I really don't know why you aren't stopping coding anything and getting
if sorted out as highest priority. Almost very bit of code being written
will have to be revisited, so you are going nowhere fast. If I have seen
this sort of futile effort once before I have seen it about 30 times
now. On quite a few I said the projects would crap out and I would just
walk out, leaving coders saying 'Don't interrupt me. Can't you see we
are too busy coding.' :-) Some projects were costing into the scores of
millions of dollars. Every single one of those projects got scrapped,
and in the timeframe I told them it would. I am not just blowing my own
trumpet here, but I know what I am talking about from many years
experience.

If you don't define clear scope and functionality and build a data model
that correctly reflects, you will go the same way.

BTW the problems with parts - assembly are caused by the same problem.
In this case its the standard component-assembly hierarchy pattern that
any competent data modeller understands.