On 4/26/07, David Tangye <[EMAIL PROTECTED]> wrote: > On Thu, 2007-04-26 at 19:36 -0700, Chris Travers wrote: > > On 4/26/07, Gerald Chudyk <[EMAIL PROTECTED]> 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. > > I second these very good points. Developing a high level model that represents the business solution will make many design choices easier. My favorite document has always been the Context E/R diagram. (My favorite mantra is "always limit scope", but that is another discussion).
Gerald. ------------------------------------------------------------------------- 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 [email protected] https://lists.sourceforge.net/lists/listinfo/ledger-smb-devel
