Hi.

Please excuse my english.
This question seems to have no answer in forums nor wiki:

* We currently have a product (not Hibernate based) which manages inventory. We do it by modeling "inventory items" in an OO way:
we have 'item classes' which define what attributes an item must have (for example, a "printer" must have 'resolution', 'interface type', 'cartigde reference', etc)
we have "container classes" that can also have attributes (ex: a "building" must have an 'address') but whose main purpose is to serve as containers for various items and or other containers.

We then establish relationships between "item" objects (say, printers, cpus) and "container" objects (say a site, a building, a city...). We create a hierarchy with "cities", "sites", "subsites", "items" etc, for one to know "where is what" in a very flexible way.

The logic for manipulalating this hierarchy, creating new "items", changing items attributes, putting items and containers inside other containers resides in separate packages which we call "business rules"

So far it might sound very applealing candidate produt to migrate from its current JDBC+SQL mess to a new and elegant hibernate implementation.

However, there is an issue:

We must offer the ability to extend the model by creating new "classes of objects" (classes of items).

This way our customers can create a new class of items (say "USB flash storage") with attributes (say 'capacity', etc ) and then be able to create new instance "items" of that class (say: Flexar with 256Mb, serial #xxx), and have those "items" interact with other objects (put the USB 'item' in a "depot" container, etc).

It is very important that our customers can create those new classes of items, and use items of those new classes at runtime, and with no "developer intervention" and no need to restart the application. Thats the feature what sells our product.

Currently, our implementation is a monster of SQL and "query builder functions" that creates a new database table for every new class the user creates (featuring one column for each attribute the user declares, and other stuff), and manages item creation/modification/deletion as inserts/updates/deletes in those tables. It also generates, compiles and loads java source code of objects that feature the desired attributes, can be persisted by the SQL_monster and at the same time can be used by the bussiness rules packages. It works, but it is slow, ugly and will not scale.

Also we would love to be able to persist "real" objects, containing the bussiness rules within themselves, instead of these "groups of attributes" we currently use.

But it is one thing to persist objects, and another thing to allow dynamic extension of the list of persistable object types.

My question is:

I think you are off track. I don't recommend creating new tables, but using an EAV (entity attribute value) approach. This is much more dynamic and exactly what you need for that "new classes of items" part of your application. It can be implemented in Hibernate, but we don't have any patterns yet. Search google for the general approach.

In a conventional table that has one column per attribute, to "fecth" one object implies only 1 query (one DB transaction).

In EAV design, one row stores a single "attribute/value". I believe it would take either multiple queries or one big-hairy-slow query to fetch an object. (if I am wrong, please tell me, and why).

As I explained, we are concerned with performance, and the nature of our objects is very hierarchical (recalling a persisted object might imply recalling a whole tree of objects, think city->buildings_in_city->stuff_in_each_building).

We do need those trees, and thought the performance cost of generating queries (cause the datamodel changes overtime) is lower than than of having to issue multiple queries.

Try to use EAV. Fully answering your questions would require several hours and pages of text, you are on your own. I implemented an application that had an EAV part (data models _only_ using EAV are _bad_, use it as an extension where needed) with Hibernate 1.x, so it shouldn't be a problem with Hibernate 2. Just go ahead and design an EAV model for that part of your application and think in SQL/relational terms. Then, implement it with Hibernate.