Multiple Schemas vs. Multiple Instances

We are faced with a crucial design decision and I'd like to benefit from those Oracle DBA's out there who've more experience than I. I understand that you generally only need multiple instances in cases where significantly different types of applications will be accessing the data. But we have a situation where we'd like to "clone" application code in such a way that it could access disparate sets of identically structured data depending upon external settings.

For example, take a table called Customer which contains the usual customer-related stuff. We want to separate customers in the auto parts industry from customers in the travel industry by placing them in separate tables that are structured identically, and in fact both called Customer. We do this because we have a large code base that performs the same for auto parts customers as it does for travel customers. Even though the apps may be running on different web servers, there's no reason to have different application code. The web server must be able to tell whether it is running a travel app or an auto parts app and access the appropriate Customer table.

Our dilemma is whether to use a design that involves separate instances for auto parts and travel, or separate schemas and dynamically assigned synonyms.  Anyone have input as to which is more scalable, flexible, and requires less administration? Is there another approach that might be more appropriate? Using all dynamic SQL is not an option. We'd rather not put an industry identifier in the Customer table because the data model is quite complex and we'd have to drag that industry id all over the place in order to keep the customers separated. It seems to us that the multiple-instance solution would require more administration, but would also be more scalable and flexible. But then there would also be some administrative costs in maintaining essentially duplicate schemas and the necessary synonyms. Your advice is appreciated.

Gary Sadler, DBA
Polygon Network Inc.
gary_at_polygon.net

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Jan 26 1999 - 19:00:31 CST