>Hi 'rybody
>
>I have a serious app being generated in VFP so I would like to have some feedback on my older schema of tables creation for any improvement specifically suited for VFP and of which the old logic of mine becomes deprecated or redundant.
>
>With experience in my DOS apps I have made it a practice and fallen into a rut, to create Parent-Child tables for each and every kind of transaction that maybe encountered by a business, say an invoice, purchase bill and so on.
>
>These transactions when saved will create respective entries in a table which contains the gist of the transaction, for eg. uStock table will contain the docno, docdate, ItemID, addqty, lessqty, sourcetablename, sourcetableIID of the Child table. uJournal table will contain the docno, docdate, AccoutID, debitamt, creditamt, sourcetablename, sourcetableIID of the Parent table. I don't know the theoritical name for such tables, if there is any name then please let me know.
>
>These u* kind of tables where the ones I used for ledger kind of reports like Stock Ledger (using uStock), Accounts Receivable / Payable / General Ledger (using uJournal). The benefit I got from this was that I had to report from a single table to start with, for eg. both Invoice and Receipts will have corresponding entries in uJournal. If I require further details I would connect to the orignating table using the sourcetableIID and sourcetablename fields. Register-like reports Sales Register, Purchase Register were generated directly from the Parent-Child tables.
>
>Now the problem arises when there is some hitch in the app, power outage, bug, user. The uStock, uJournal tables are no longer in sync with the Parent-Child table sets. This also gave rise to variation in reporting values for the same set of Parent-Child tables, because uStock, uJournal has more, less or wrong entries, it will report different results in the Ledger based report from the Register based reports which report directly using the Parent-Child tables. These problems are few and far between after all these years (initially... don't ask me how bad it was), but when they do occur they are not good for my heart *g*.
>
>I would appreciate if you'll can share your views, suggestions, improvements, critical analysis, anthing regarding the above mentioned schema. I would like to remove as many of my planning limitations as I can before I embark on the VFP version of a similar app.
>
>Thanks for taking out the time to read this detailed post.
>
>Awaiting responses.

Bhavbhuti,
If I understood correctly, u* tables are support tables for information deriving data from real data entry tables and thus could be rebuild from other tables anytime, right? If that's so, then instead of keeping and updating them as tables I think it would be better to create them as views or no views at all, just specialized routines to build them as cursors anytime. Downside of this approach is that either a view or a cursor needs time to build whereas a support table is ready out of the box. However since reporting/printing etc are generally done with some filters that needed time might be as short as a blink of eye. Pros of this approach is that you avoid the redundancy of data. With less tables it's also easier to create and maintain and/or transfer this in another backend - say SQL server. Assuming in future or now you move this to SQL server, SQL server can partition a table based on some criteria (ie: based on year, it creates a single physical table for each year, you still work with a logical single table and it manages into which physical 'table' the data would be placed, when queried it manages which physical tables would be used to pull etc. - depicted here as I see that one of the best features of SQL server:). Or you could create a table valued function that is taking parameters and returning a table as a result (similar to views where views cannot take parameters, higher performance and managebility as I experienced). Lots of other new features that make u* unnecessary (if I understood their purpose right - audit tables?).
PS: Sorry to be thinking in terms of SQL server additionally:) It continues to get my increasing respect with SQL2005.
Cetin