Hello,

I'm developing an application which involves some geographic data such as states, countries and area codes. The database tables look like this:
The database model is:


Table T_COMPANY
COMPANY_ID (PK)

Table T_COUNTRY
COUNTRY_ID (PK)
COUNTRY_NAME

Table T_STATE
COUNTRY_ID (PK)
STATE_ID (PK)
STATE_NAME
(COUNTRY_ID is fk from T_COUNTRY)

Table T_AREA_CODE
COUNTRY_ID (PK)
AREA_CODE (PK)
STATE_ID
(COUNTRY_ID + STATE_ID are fk from T_STATE)

Now I want to maintain information about routes each company works in which, in my case, are defined by pairs of origin area codes with destination states. So the company routes table looks like this:
Table T_COMPANY_ROUTE
COMPANY_ID (PK)
COUNTRY_ID (PK)
ORIGIN_AREA_CODE (PK)
DEST_STATE_ID (PK)
(COMPANY_ID is fk from T_COMPANY)
(COUNTRY_ID+DEST_STATE_ID is fk from T_STATE)
(COUNTRY_ID+ORIGIN_AREA_CODE is fk from T_AREA_CODE)

I've mapped the area code, state and country to classes quite easily. Now when mapping the company class I want to map all its relevant routes to a collection of Route class I defined (which is defined by AreaCode object and State object. Of course I want the mapping to be for entity references and not just IDs. I tried to define a bag with composite elements but I think I'm not in the right direction since only the keys of the related objects were initialized and the objects were not retrieved from their parent tables.
I'm looking for a mapping that will provide the following behaviour:
1. State object will be constructed and read up by the value of the
columns COUNTRY_ID and STATE_ID
2. AreaCode object will be constructed and read up by the value of the
columns COUNTRY_ID and AREA_CODE
3. Route object will be constructed by the resulted State and Area Code
objects that were made in phases 1 and 2.
I really don't know how to manage to do all this within the single bag element.

Thanks!
Dror