Re: Too many schemas!

Donatella,

After looking at the other suggestions, I won't go into the security. Fine Grain Access (row level security) is a good choice. Just remember NEVER trust stuff coming from a browser. You don't know who wrote the browser!

Let me get you thinking in another direction. Performance. Having one schema (in this case I think you mean one table) can produced some interesting performance issues. You should be looking at partitioning day one and design in a method to determine usage by individual client (to give you data for your partitioning decisions). Bottom line is simple: 1,000 clients mean maintenance issues down to the client level no matter how you look at them.

Don't under estimate your performance problems with a single table/schema, if each client has on average just 10 users, you have a user base of 10,000 people with access to the table. If just 5% of the clients end up being heavy hitters, you're talking about 500 heavy hitters each day! You need to design in ways to manage the performance from day one.

Bertram Moshier
Oracle Certified Professional 8i and 9i DBA

http://www.bmoshier.net/bertram

"Donatella" <donatella_at_webadorable.com> wrote in message news:ff4c959.0211170430.4cea7752_at_posting.google.com...
> I am working on a three-tier architecture for a web-hosted
> application, with browsers at the front, J2EE in the middle, and
> Oracle at the back.
>
> My experience is mostly in J2EE and until now I have only worked with
> single-client models.
>
> This application is intended for many clients - each with their own
> set of users - working with their own separate data.
>
> Initially I was imagining having many identical schemas, one for each
> client.
>
> But with 500-1000 clients this seems unmanageable when it comes to
> implementing future changes to the database.
>
> Should I be considering ONE schema shared by all the clients?
>
> That would mean tagging ALL the records with client IDs which would
> predicate all data queries and transactions.
>
> This must be a common issue with web-hosted applications.
>
> Any pointers to online resources and/or books which deal with this
> would be much appreciated.
>
> Many thanks!
>
> Donatella
Received on Mon Nov 18 2002 - 15:55:21 CST