I would like to use "proper way" of mapping my database so I was hoping to get some advice on the following schema setup:

TABLE Customer (primary key: customer_tkn)
TABLE BillingPackage (primary key: customer_tkn, billing_pkg_tkn)
TABLE BillingAddress (primary key: customer_tkn, billing_pkg_tkn, billing_address_tkn)

I would like to have Set Collections in Customer and BillingPackage that use lazy loading and so far I got BillingPackageSet working in Customer class but BillingAddress set in BillingPackage class is not working.

here is relevant mapping data:

<class name="Customer" table="customer">

<id name="customerTkn" type="java.lang.Integer">
<column name="customer_tkn" not-null="true" />
<generator class="assigned"/>
</id>

<set name="billingPackageSet" lazy="true" cascade="all-delete-orphan">
<key column="customer_tkn" />
<one-to-many class="BillingPackage"/>
</set>

</class>


<class name="BillingPackage" table="billing_package">
<composite-id class="BillingPackagePK" name="id">
<key-property column="customer_tkn" name="customerTkn" type="java.lang.Integer" />
<key-property column="billing_pkg_tkn" name="billingPkgTkn" type="java.lang.Integer" />
</composite-id>

<set name="billingAddressSet" lazy="true" cascade="all-delete-orphan">
<key>
<column name="customer_tkn" />
<column name="billing_pkg_tkn" />
</key>
<one-to-many class="BillingAddress"/>
</set>
</class>

<class name="BillingAddress" table="billing_address">
<composite-id class="BillingAddressPK" name="id">
<key-property column="customer_tkn" name="customerTkn" type="java.lang.Integer" />
<key-property column="billing_pkg_tkn" name="billingPkgTkn" type="java.lang.Integer" />
<key-property column="billing_address_tkn" name="billingAddressTkn" type="java.lang.Integer" />
</composite-id>
</class>


Again...i am able to use billingPackageSet to view BillingPackages but I am not able to view billingAddressSet from withing BillingPackage.

(I am keeping/using session when accessing data - I know it is requirement with lazy loading).

hibernate: version 2.1.2
database: oracle 9i

one more thing....You guys made one awesome product ;-)

thanks
vlad

Look OK to me but sounds like you have control over the database, so don't use composite keys at all. I hate them and avoid them if at al possible (which is hard to do at times).

TABLE Customer (primary key: customer_tkn)
TABLE BillingPackage (primary key: billing_pkg_tkn, foriegn key: customer_tkn)
TABLE BillingAddress (primary key: billing_address_tkn, foriegn key: customer_tkn, foriegn key: billing_pkg_tkn )

Its much cleaner, the primary keys now don't have to have any business meaning at all. So much easier when you need to maintain the database for changes in business processes in the future. Its also easier to map, query, and maintain. You will thank me at some point in the future.

Any chance you could give me example of how i should change my mapping to accomodate foreign key use?

much appreciated
vlad

I use mapping for composite id like this :


regrads

The above example is one for composite keys (using your original structure). This is fine if you going to keep the composite key design.
My suggestion is to remove the composite keys. Then you use normal parent-child mappings based on the (foreign key) constraints, eg, many-to-one and/or one-to-many's appropriately.

Why database vendors (oracle, postgresql) have own dictionary with composite keys, if composite keys are bad desing choice ?

regards

I am not a fan of composite keys in most cases. They usually result in difficult to understand and maintain schemas. The key's contents usually carry business meaning which (in my experience) complicates matters by reducing flexibility when managing the data. Your application of them maybe top class and if you prefer them then all the power to you. Vendors support them because there are cases they are necessary. Even if a programming language has goto's you generally use other program flow constructs before selecting the goto. Obviously, if its appropriate for a particular use case then use it. The majority of developers (that I have crossed paths with) automatically think of (and use) composite keys when other options are available. I have only suggested an alternative (I think superior) approach for consideration. I don't expect everyone to agree but my mind is open to consider others approaches and solutions. Thats how I grow as a professional in this business.

Oracle have simple schema (order entry) for example application :

order_items table have primary key (order_id,line_item_id)
inventories have primary key (product_id, warehouse_id)
product_descriptions have (product_id, language_id)
etc

regards

I tried mapping my three tables without using composite-keys but i had a problem with mapping billing_address which has customer_tkn and billing_pkg_tkn as foreign keys.

Any suggestions on how BillingAddressSet in BillingPackage should be defined and what is needed in BillingAddress mapping itself?

thanks

upgrading to version 2.1.6 (i was on version 2.1.2) and adding (well heving to add) hashCode to PK classes fixed my lazy load problem ;-)

Glad you sorted it out. It is a requirement that you supply equals and hashcode implementations when using composite keys.