Re: Please advice: Multiple Schemas or multiple db

I think you've answered your own question: if the number of companies is expected to grow to hundreds in the future, you would be stark raving bonkers to go down the 'one database per company' route.

Oracle would love you (licensing fees!), but it's really a non-starter.

You're going to have to go the multiple schemas per database route (doesn't mean ALL schemas have to be in a single database, though... maybe two or three databases, with the schemas distributed amongst them).

The usual objection to multiple schemas (and one I support strongly) is that when they are within a single database, the failure of that single database (hard disk crash, etc) compromises the availability of lots and lots of companies' data. What's more, it's an all or nothing approach: a single database is either producing archives, or it isn't. Maybe some of your companies would warrant archives, and some not... well, with a single database, you've got no choice. Likewise, backup schedules become a one-size-fits-all affair. Then there's performance: the work done with one company's data hogs resources that might be needed for another's.

Standard advice, as a result of these factors and others, is to put one database per machine. With multiple databases, you have flexibility about archivelog, and backup schedules. The loss of one database doesn't take out all the other data with it. The I/O you do on one database makes no difference to the others.

But all that is moot: your projected numbers dictate a multiple-schema approach, unless you have inifnite quantities of money and hardware, and particularly relish the thought of attempting to manage hundreds of databases.

Regards
HJR "Ak" <a_sood_at_rediffmail.com> wrote in message news:10cb5393.0208061319.33ad0c98_at_posting.google.com...
> Hi,
> I have an 8.1.7 db running on Windows2K, the db will house data for
> multiple companies. I am contemplating whether to house each company
> in an individual schema or ceate seperate databases for each comapany.
> The number of companies would grow to hundreds in the future. I can
> house all companies under the same schema, but this would limit my
> ability to migrate companies selectively to newer versions and make
> the migration process a nightmare as I would have to migrate every
> company (all data) at one time.
> Which would be a better alternative, as far as performance,
> managability, and cost involved.
> I am aware that many other factors would affect such a decision, but I
> am hoping to get inputs based on similar experiences.
>
> Thanks!
> Ak
> OCP,Database Administrator
Received on Wed Aug 07 2002 - 17:15:15 CDT