Bob,
Does the data in A that B needs change frequently, or is it rather static? If it changes, do either of these databases grow and shrink, or are they always growing? In our app, we have four audit databases – one for each of three major inventories, and one for orders. We set these up as separate databases because they will continually grow, whereas the main transactional database waxes and wanes. All of the writes to the external audit databases are handled by triggers. The code will do the SCONNECT, write the record, then SDISCONNECT. Since R:BASE remembers the structure of the SATTACHed tables, there is no need to redo the SATTACH then SDETACH every time. These transactions are always INSERTs. When we need to retrieve data, a similar process is undertaken. The speed of the reads from the external databases is greatly impacted by judicious use of indexes. So, bottom line here is that you can leave the two separate and SCONNECT/SDISCONNECT on demand. I see no penalties to this process. By the way, Oterro 4 is in use for the ODBC. The option if the data doesn’t change much is to use R:Synchronizer periodically to synchronize the data from A to a matching table in B. Either of those options retains your semi-independent approach. But, if you can foresee other future interdependencies between them, do the merge. Emmitt Dove Manager, Converting Applications Development Evergreen Packaging, Inc. [email protected] (203) 214-5683 m (203) 643-8022 o (203) 643-8086 f [email protected] From: [email protected] [mailto:[email protected]] On Behalf Of [email protected] Sent: Monday, August 09, 2010 09:53 To: RBASE-L Mailing List Subject: [RBASE-L] - Database design question I would like some feedback or thoughts about a database design scenario. I currently have two databases, both used in a manufacturing production floor environment. I had originally made two separate databases as they were un-related operationally and thus reduced the chance that if one database went "down", it would not effect the other. Being a production system, effecting many people, jobs, operations, etc., it is imperative that down time does not happen or at least is kept to a bare minimum. Both these databases see fairly high volume of user access. Both writing and retrieving data. However, Database "B" now needs to obtain and write information to a table in Database "A". It will do so frequently, many times per hour by several operations at random times. So in essence, the two databases will be "connected" 100% of the time. So the question is... do I now merge both databases into one or keep them separate and use an ODBC connection between "A" and "B". Since "B" now needs data from "A", the original purpose of being separate is now gone.... I.E. If "A" goes down, so will "B". I ask this as I assume that an ODBC connection is not as efficient as a direct database access. Does not an ODBC connection have to call up a session of RBASE as well, even if both databases are in RBASE? What are thoughts on keeping all the data in one DB versus the two? (Database size will not be an issue in this case) Thank you, -Bob
