Scott Leberknight's Weblog

On my current project at work, we started out with our own separate database. About a month into development, we were directed to re-host our database inside a centrally managed corporate database. The application itself is relatively small with a small number of tables - around 20 in total. Prior to our move into the corporate database, we had our own schema in an Oracle9 database. This worked out well since our tables could be named exactly as we wanted and we didn't need to worry about naming conflicts.

When we began the migration into the new Oracle9 database, however, we found out that we would not have our own schema. Instead all our tables would be created in one schema, as there is only one schema for the entire database. Because there is only one schema, this means that all our tables had to be renamed in accordance with a set of naming conventions and also to ensure the table names fully described the domain and intent of the table. In other words, since all tables are in one schema, the table name is the fully qualified unique name. At first glance this "One Schema To Rule Them All" approach did not seem to be the best idea.

For one thing, tables in different Oracle schemas can certainly see each other so long as the appropriate permissions are granted. They can also reference each other via normal foreign key constraints. In addition, the fully qualified name of a table is schema_name.table_name such that you could have two tables with the same name in different schemas and not have a naming conflict. Also, requiring all tables to reside in one schema means that you cannot have two tables with the same name, and you sometimes would end up having to create contrived names to avoid name collisions.

But I think the real reason why this approach struck me as not such a good choice was related to the way in which you package classes in object-oriented languages such as Java and C#. In Java you create packages and you place classes into those packages according to some logical breakdown. For example, in a web application you might have high-level packages representing the view, the model, and the controller. Or you might create packages according to use cases. And more than likely different applications will reside in different packages, with perhaps some common packages in a "common" or "utils" package that can be shared between applications. So I thought: Wouldn't it be better to create a "core" schema in which tables common to all applications, e.g. Employee, Department, etc. could reside and then have separate schemas for each application?

So I asked several of the database designers we are working with about this. The answer was that several years ago they actually started down the multiple schema path, but quickly found that establishing the permissions between all the schemas coupled with some additional security restrictions they have in place was a real maintenance nightmare. I suppose that makes sense, since in order to have access to the proper tables each database account needs to be granted permissions on all the schemas and tables within those schemas. And with the additional security features they require it complicates this further. Thus they changed to the one schema approach and have strict naming guidelines and conventions to handle potential naming collisions.

I can understand this argument, but I wonder if there still isn't an easier way to deal with this issue in large corporate databases shared by multiple applications across multiple business units. Until then, we have "One schema to rule them all, One schema to find them. One schema to bring them all and in the darkness bind them."