[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Thoughts on payment handling in 1.4.x

Hi David;

The model was a vague one with only minimal items shown for
illustration (hence the reference that the table model could be far
more complex than mentioned).

Most of the database design work is being done by Josh Drake, and he
is an expert in such things and if he doesn't do it, I am sure he will
provide some strong feedback on the data model.  One of our major
tasks is making the database design follow the relational model
properly.  This means proper normalization and data design.

We are aware that relational databases are math-based constructs, and
should not be approached from the perspective of the program logic
(except for the stored procedures).  Unlike the current schema which
seems to use the database as "a place to hold the data" we intend to
do it right and have the expertise to get there.  But doing it *right*
takes time so please have patience :-)

Best Wishes.
Chris Travers

On 4/26/07, David Tangye <..hidden..> wrote:

On Thu, 2007-04-26 at 19:36 -0700, Chris Travers wrote:
> On 4/26/07, Gerald Chudyk <..hidden..> wrote:
>
> > Are you planning total integration between accounting modules? I
> > designed a gl like this once. The issues with ar and ap are fairly
> > clear; each transaction is a db/cr to a few well defined gl accounts
> > with a reference to customer/vendor id.
>
> One of the goals is to normalize the accounting data so reporting is
> easier and you don't have any potential data imbiguity issues.
>
> The idea (as I currently understand it) would be to have at least the
> following tables:
> invoices
> invoice_items
> journal
> journal_lines
>
> Maybe more.  I am thinking that it would be best to have the secondary
> transaction in the journal_lines table because one journal line should
> have either 1 or 2 (but no more) transactions associated with it.  And
> only one is a primary transaction.
>
> > The problem is found in the number of ar transactions this creates and
> > the inability to optimize modules for speed or size. Some modules,
> > like oe will probably not do well; many oe activities are not needed
> > in the gl until a sale is finalized.
>
> Of course.  Not quite sure how OE will be handled yet.  But orders are
> not financial transactions so would not appear in the general journal.
>
> > This approach forces the gl to become more than a general ledger and
> > instead becomes an itemized repository of every activity in the
> > system. I think there is a better way to do this.
>
> No, only financial transactions.  Non-financial-transaction items
> (order entry, quotations, warehouses, pricematrix, contact management,
> etc) fall outside this.  In essence the general journal does exactly
> what it does in the paper accounting world-- acts as the first point
> of entry for any financial transation.

I remember this sort of discussion coming up beforehand, and I shall
briefly give the same sort of advice again.

The above is mixing the problem, analysis, logical and phyical design
into one. At the start of my several years (over 10) in database
modelling and design, that was about the first thing I was taught not to
do. In a very simple nutshell: First define terms, then make a logical
model (entities, relationships and keys and attributes - all clearly
defined; plus subtype entities). Only then decide how to physically
model (ie tables and columns, keys and constraints). If yo attempt to
mess the exercise into a discussion covering all this at once, you will
only get confusion and contradiction.

eg

  Entity: TRANSACTION
  Entity: FINANCIAL_TRANSACTION (subtype of TRANSACTION)
  Entity: BUSINESS_TRANSACTION (subtype of TRANSACTION)
  Entities: REQUEST_FOR_QUOTE, PURCHASE_ORDER, SALES_ORDER, INVOICE,
DELIVERY, TIME_WORKED(ie timesheet stuff), STOCK_TRANSFER, etc (common
subtypes of BUSINESS_TRANSACTION)

Define their natural keys. Define attributes. Based on relationships and
attributes, should some particular entities even exist in their own
right? Etc etc

Scope the system fully, in terms of functionality, so you can include
all entities that are subject of that scope.

Then, and only then, decide how to represent them at tables. eg do you:
        a - have one table for a supertype and its subtypes plus do you have an
entity_TYPE table so you can expand the subtypes relatively easily, at
the cost of more complex programming upfront, or do you
        b - have separate tables for the supertype and also for each subtype
(or just for each subtype only).
Whichever way you design the physical schema, sql views can be created
to reflect the alternative views a or b as per above. There is not
usually a correct way, a or b, except the development and runtime
softwaare technology might lean you one way or the other. The important
thing is to be consistent and to document the reasons for those
particular design decisions.




-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Ledger-smb-devel mailing list
..hidden..
https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel