Re: Instance with thousands of schemas

In article <Xns902DA022CCF33audun.jyahoo.no_at_130.133.1.4>,   audun_j_at_yahoo.no (Audun Jensen) wrote:
> Hi,
> I'm starting to migrate a MS SQL Server db to Oracle and have some
> questions I would like to have comments on.
> Today our SQL Server database consists of more than 3500 user
 databases and
> the number is increasing. Each database corresponds to a
 user "project". As
> some of you probably are aware of, SQL Server have a "database"-
 concept
> that differs from Oracle's, in the way that one SQL Server instance
 can
> consist of many databases, each database consisting of users, tables,
> views, procs, triggers etc.. I believe the way to do this using
 Oracle will
> be to create and implement a schema for each database, containing the
> necessary objects and constraints.
>
> This leads to an instance containing thousands of schemas, each
 containing
> 50-100 tables. In addition I will have to create users who can access
 one
> or more of these "databases" depending on how many "projects" the
 customer
> creates.
>
> Is there anybody who have any experience in this number of schemas?
 What
> will I have to think of in order to have an instance that is
 operatable and
> running smooth?
>
> TIA
>
> AJ
>

Here is a radical idea. Redesign the database. Thousands of schemes seems a bit silly. If each one is really a 'project' and each project is the same(?), why have separate schemas? If each project is different -- basically, a unique set of tables; the implication is that you have a thousand database designer/developers. Most likely, the projects are very similar or evolved from a parent project. Properly structured, one could have a single schema of 'project' with access privileges to each 'project'.

Migration could be initially any 'project's that are currently underway. Others could be converted later. New projects would be added into the new schema.

Working on a design to replicate a bad design, leads to another bad design. Garbage in, garbage out. One has too many constraints in doing good work to include a poorly concieved prior project.

I have seen many projects fail due to poor design. A good rule of thumb in any design, if it sounds silly, it often is.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/