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 <[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. > > > > > ------------------------------------------------------------------------- > 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 > ------------------------------------------------------------------------- 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
