Like many of the posters I am new to Hibernate and come with a DB background - so please bear with me.

I would like to store multiple versions of invoices in the Invoice table with a different primary key for each record - but each Invoice having the same surrogate key:

TABLE tblInvoice (
InvoiceID (unique for each row)
CurrentVersion (indicates the latest version of the invoice)
InvoiceSurrogateKey (unique for each invoice)
......
)

Linked to the Invoice table is an Audit table:

TABLE tblAudit (
AuditID
InvoiceSurrogateKey
.....
)


There is a many-to-one relationship between audit records and invoice records providing the invoices are restricted to CurrentVersion = 'Y'. Is there a way to model this using Hibernate many-to-one and one-to-many elements?

Here is my attempt that does works fine in the one-to-many case (Invoices->Audit) but not in the many-to-one case.

What is the best way of approaching this in Hibernate? I don't really want to have a separate table of historical records to store old versions of Invoices if I can avoid it.

Any experiences / ideas?

Alex


<hibernate-mapping package="com.dytech.internal.timepiece.hibernate">

<class name="Invoice" table="tblInvoice">
<id name="invoiceid" column="InvoiceID" type="java.lang.Integer">
<generator class="native"/>
</id>

...
<property name="currentversion" column="CurrentVersion" type="java.lang.Boolean" not-null="true" />
<property name="invoicesk" column="InvoiceSK" type="java.lang.Integer" />

<set name="invoicestatushistoryentries"
inverse="true"
cascade="all,delete-orphan">
<key column="invoicesk"
property-ref="invoicesk"/>
<one-to-many class="com.dytech.internal.timepiece.hibernate.Invoicestatushistory" />
</set>

<class name="Invoicestatushistory" table="tblInvoiceStatusHistory">
<id name="invoicestatushistoryid" column="InvoiceStatusHistoryID" type="java.lang.Integer">
<generator class="native"/>
</id>

<property name="date" column="Date" type="java.util.Date" not-null="true" />
<property name="percentcomplete" column="PercentComplete" type="java.lang.Integer" />
<property name="sortorder" column="SortOrder" type="java.lang.Integer" not-null="true" />

<many-to-one name="invoice" property-ref="invoicesk">
<column name="invoicesk" />
<formula> (currentversion='Y') </formula>
</many-to-one>

</class>

</class>



</hibernate-mapping>

I may have a solution:

Create two subclasses of the versioned object -

CurrentInvoices
HistoricalInvoices

using CurrentVersion (Y/N) as the discriminator - then use the CurrentInvoices class when I set up the joins to the table - as that should leave a many-to-one and one-to-many relationship.

What does anyone more experienced think of this solution? Do you think it would work? Is there a better solution?

Alex