JBoss 3.0.3, Windows
Consider these tables:
product - product information, pk = productId
supplier - supplier information, pk = supplierId
product_supplier - information about a product from a spcefic supplier, pk = xrefId, fk1 = productId, fk2 = supplierId.
product is the 1 side of a 1-N relation with product_supplier. supplier is the 1 side of a 1-N relation with product_supplier. So naturally product_supplier has the productId and supplierId set up as CMR fields and NOT cmp fields.
The problem is I also want to find product_supplier records by exact match on the two CMR columns. I can't create a finder for them, even though the column exist, because they cannot be cmp fields as well as cmr fields.
The only option I see is to add two more column to use when for the exact finder. These columns would be duplicates of the productId and supplierId columns (different names, of course), and would have to be kept in sync somehow.
This seems to be a sinful waste of space and indexing overhead in a database table.
Does anyone know a way around this? Is there some way to fool JBoss into allowing a finder on the CMR fields?
One thing to do is to just use the 1-N CMR accessor from one of the master tables and search the collection returned. But there could be thousands of results and that is just not efficient.
There has to be a better way!
Thanks,
-Don
