Hi all,

I'm having a relational model with composite IDS to map zip codes to area codes in countries, allowing the flexibility to have more than one area code per zip.

The database model is:

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)

Table T_ZIP_CODE
COUNTRY_ID (PK)
ZIP_CODE (PK)
ZIP_LATITUDE
ZIP_LONGITUDE
(COUNTRY_ID is fk from T_COUNTRY)

Table T_ZIP_AREA_CODE
COUNTRY_ID (PK)
ZIP_CODE (PK)
ZIP_SEQ (PK)
AREA_CODE
(COUNTRY_ID+ZIP_CODE is fk from T_ZIP)
(COUNTRY_ID+AREA_CODE is fk from T_AREA_CODE)

I managed to map everything ok except the many to many relationship between ZIP and area code. I am only interested in the relationship from the ZIP side, so the area code mapping is OK.

Here's the mapping for the ZIP code class:



When trying to run this I get the "Repeated column in mapping for collection" because COUNTRY_ID is used both as part of the key to access the T_ZIP_AREA_CODE table and as part of the key of an AREA_CODE from T_AREA_CODE. But logically this is ok because it is indeed the same country the one for the zip code and the one for the area code.
A possible workaround for this, I believe, will be adding additional COUNTRY_ID column to the db table and using one for access to the link table and one to define the area code key, but this is awkward.

Anybody know how to get this working right with those composite-ids?
I have also defined a class for composite key for area code - can it be used here in some way?

Any help will be appreciated.

Please let me know if you were able to solve the issue. I am having the same issue.

I couldn't get the complete the mappings properly using composite keys. Luckily, I had the possibility of modifying the database schema and introducing surrogate (or synthetic) keys for the entities which have composite natural candidate solves the problem immediately so you don't have to combat those definitions.
So, for example, I had to introduce a new synthetic unique key column for table T_STATE instead of using COUNTRY_ID and STATE_ID. Once you do this many mapping chalenges are solved.
Although I prefer the database schema with the composite keys, making this change helped me complete the mapping for my application quite easily. Yhey argue that natural candidate keys are easy for humans to understand, but synthetic keys are easier for the computers.

Good luck with your application
Drpr

Thank you very much for your response. We are also working on changing the database tables.