Hi,
I hope this small "philosophical" topic is not off-place here.
Since I'm migrating a legacy DB app to Hibernate I'm facing lots of issues related to the old design of the original application. But since the new application has a new database, I had the chance to modify the schema.
I've got a copy of Hibernate in Action and went on.
I've migrated all composite keys to generated keys, and put the old composite keys as "business keys" in a unique index. This seems to be the best option for me, and I like very much the clean design of generated keys.
Now, when it comes to relational mappings (many to one, lists...), I began to have "problems". All tables in our application have a column called COMMITTENTE (in english we can call it OWNER). The OWNER is the application user, owner-side. Every OWNER only see his data. This kind of design has been always very flexible to our needs, in particular for outsourced applications.
I'm a bit unclear on how to manage, for example, many-to-one relations keeping the OWNER thing. In practice :
ORDER_ROW has a long ID generated key, an OWNER char property and an ITEM_ID char property which would be the obvious relation to the ARTICLE table. But to do the correct "business" relation I should use both OWNER and ITEM_ID to relate to the ITEM table. I should something like :
ORDER_ROW mapping file
Code:
<id name="id" column="ID" type="long">
<generator class="sequence" />
</id>
<!-- my business key -->
<property name="owner" column="OWNER" type="string" length="5" not-null="true" />
<property name="orderId" column="ORDER_ID" type="string" length="5" not-null="true" />
<property name="rowNumber" column="ROW_NUMBER" type="integer" not-null="true" />
<!-- end of b. key -->
<property name="itemId" column="ITEM_ID" type="string" length="20" />
ARTICLE mapping file
Code:
<id name="id" column="ID" type="long">
<generator class="sequence" />
</id>
<!-- my business key -->
<property name="owner" column="OWNER" type="string" length="5" not-null="true" />
<property name="itemId" column="ITEM_ID" type="string" length="20" />
<!-- end of b. key -->
So... I should do a many-to-one mapping from ORDER_ROW to ITEM, which uses MULTIPLE columns (OWNER and ITEM_ID) which are NOT the primary key in the ITEM table! I'm not sure this is supported in Hibernate, since my need is not very object-oriented. With my knowledge of Hibernate, I can do a many-to-one mapping from and to ONE column which is not the primary key (using property-ref), and this should work (but is not enough in my design). The older ORM layer (custom made) supported those kind of things, because composite keys were very common in that moment.
By the way : one option would be to use the ITEM generated key in the ITEM_ID column of ORDER_ROW. In my experience, I feel that is not right to use generated keys for business many-to-one mapping. The generated key, in my opinion, is great for ORM handling and all, but in my ORDER_ROW the ITEM_ID column should contain the business ITEM_ID, not the generated ID of the ITEM table.
Now, I'm accepting all kinds of suggestions to improve this kind of design. I want to use Hibernate in my application, so I hope there is some way to force my design to work.
TIA.
Giulio Vezzelli