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."

Thanks to Novajug I now have two new books. Two weeks ago I won "Core JSF" and last night I won "Eclipse in Action" in random drawings of business cards from a container. Now if I only had some time lying around to read them. The only reason for posting is simply that I rarely win anything from those types of drawings. Can I make it a three-peat at the next meeting? :-)

A week or two ago I went to the Novajug Enterprise Group meeting to hear about Hibernate3. Steve Ebersole from JBoss gave the presentation and did a good job explaining the future direction of Hibernate. From what he said, it appears the Hibernate crew have done a significant amount of work, including internal refactorings in addition to a bunch of new features I definitely want to get my hands on.

Steve called Hibernate3 an "evolutionary new version" of Hibernate which includes improvement of the existing code base and new features. One interesting thing is that you can actually run Hibernate3 in the same application as Hibernate2. Apparently this is possible because they changed the package root from net.sf.hibernate to org.hibernate. Of course that means to change from Hibernate2 to 3 you will need to do some search and replace in your own code. One thing that will be really cool if it happens is an improved Hibernate GUI console application that will allow you to write and test HQL queries as well as work with actual objects to see how Hibernate will behave in a real application. Another nice feature is the support for JDK 1.5 annotations, which might in some cases be a better alternative than writing .hbm.xml mapping files.

Several of the more notable internal refactorings include a new event-based architecture as opposed to the current Session-based architecture. Another new thing is the ANTLR-based HQL parser, which should provide much more granular and useful error messages for HQL syntax problems. Also, multi-valued cascade settings, e.g. cascade="save,update" instead of all the various values rammed together into dash-delimited string like cascade="save-update". One other notable change is that fetching will now default to lazy rather than eager.

Now for the new features. Hibernate3 adds Filters, or "parametrized application-level views". For example, you could implement a security filter that would only return rows in a table for which a user has the proper access level. This is pretty much like adding additional restrictions to the WHERE clause, e.g. WHERE :userLevel >= access_level, but in a dynamic and non-intrusive manner.

"Houston, we have multi-table mappings!" According to Steve, multi-table mappings are not really a good thing, as he believes you should not make your object model less granular than your database tables, e.g. by mapping two tables into one domain object. I agree most of the time. However, many of us live in a world where DBAs sometimes make the database extremely normalized to the point where one logical entity is scattered across two or more tables. I don't necessarily like it, but it's reality and I am glad Hibernate now has the ability to do multi-table mappings. Oh, did I mention you use the <join /> element to accomplish this feat? There are some more advanced things you can do like mixing <join /> and inheritance.

Another interesting, though perhaps dangerous in the hands of the wrong developer, feature is called "representation independence". Steve's example was persisting "maps of maps" or "lists of maps", etc. In other words, you can persist a dynamic model without even needing a class. I can see scenarios where this would come in handy, but would certainly be wary of using it unless there is no other good alternative.

Several other new features include JMX integration; support for handwritten SQL and stored procedure integration into normal POJO domain objects; and EJB3-style persistence operations. Though, as long as Hibernate is around I don't think I'll be knocking down EJB3's door anytime soon.