Hi all

I have handled multi-companies from the same app, but each of these companies are independent (and physically) seperate databases and tables.

Now I have been approached by a client who has multiple companies under a parent company. These companies are in themselves indepedent as well as require to be consolidated at the time of reporting. Plus they have transactions between themselves as well as the outside world.

Please let me know how to handle such database(s)?

I am showing a simple schema as per my understanding of their requirements below:

Master (lookup?) tables, which are to be shared by all companies so an update to these will immediately be available to the other companies

Accounts                Items
---------------         ---------------
iID    Integer          iID    Integer
cName  Char(35)         cDesc  Char(35)

Opening Amount/Qty. tables for each and every company. Assuming there are 1 parent + 4 children companies, each of the 5 have 100 qty. of Op. Stock for ItemA, the parent company when reporting consolidated will show ItemA as 500 qty.

OpAccounts              OpItems
----------------        ------------------
iAcctID  Integer        iItemID    Integer
nOpBal   N(12,2)        nOpStock   N(12,2)

Transaction table(s) for each and every company but these transaction will not only involve outside party accounts and items but will also involve transaction between each other of the parent and children companies

AccountsJV                     StockJV            
----------------------         --------------------
dDate          Date            dDate        Date
iDebitAcctID   Integer         iAddItemID   Integer
iCreditAcctID  Integer         iLessItemID  Integer
nAmount        N(12,2)         nQty         N(12,2)

The final app though in VFP6 will be using FireBird as a backend accessing the data via remote views.

Anybody practically solved such a problem?