Hi, I have a database which I cannot easily change and have a general problem that the database is riddled with composite keys. This seems to be a major barrier to the adoption of NHibernate because it makes the mapping so cumbersome. Basically in the system there is a concept of a business area. Many of the tables have their own key and also a business area id and the two are used together to form the primary key of the table. I would like to give an example and ask for help on how it should be mapped.

Consider the following example. I have an entity called an Item. This Item can have 0 or many MetaData records attached to it. The Item table is as follows (irrelevant columns omitted):

Item
BusinessAreaID (pk, fk)
ItemID (pk)
ItemName

The MetaData table is as follows:

MetaData
MetaDataID (pk)
Name

The link table is as follows:
ItemMetaData
BusinessAreaID (pk)
ItemID (pk)
MetaDataID (pk)

The business area table:
BusinessArea
BusinessAreaID
Name

My problem is the fact that I need BusinessAreaID in the link table. The only way that I have been able to do this is to use a composite element when mapping the MetaData collection of the Item object as follows:



This sort of works but it has two serious drawbacks. One is that the BusinessAreaID is not considered part of the key of the link table, it's just another column. This means that when I retrieve the MetaData for an Item I have to artificially filter the results on BusinessAreaID which seems clunky. Secondly it means that I have to have a class to represent the records in the link table. This class does not really serve any purpose in my model and is a bit of an eyesore that I have had to create to overcome this technical problem. This seems a little unsatifactory.

A secondary problem is how to map the idetity of the Item class itself. In reality this table has a primary key made up of the itemID and the businessAreaID (which is itself a foreign key to the business area table). At the moment I am just mapping the ItemID column as the identity and having BusinessAreaID as another property. I'm 99% sure this is wrong but don't know how I should be doing it.

I'd really appreciate some advice on this problem of pervasive composite keys so that I can remove this barrier to using NHibernate which I think can really save us a lot of work.

Julian.

PS - I have tried to read the documentation on this (and read Hibernate in Action etc), that's how I've got as far as I have, but I still feel that I must be missing something. [/code]