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.