Dear all,

I am working on a project on which I had to model Countries and Cities. A country has a year, name, population and a set of cities. Its population and city set may be different on different years. A city has a year (same with the country it is tied to), name and population as well.

I would like to keep annual records of these two entities for, say, last 20 years. A country, for instance, has an average of 100 cities (at each of these 20 years). Annually, city names and populations may vary. Without considering the redundancies, if I intuitively map this, for a single country I would have 1 + 20 * 100 = 2001 records stored. Thus, I would like to keep data stored in a City as small as possible. (20 * 100 coefficient in the calculation above)

The dominating data in a city is the city name, which can be a string. So I thought perhaps I could use an integer (plate number) to represent each city. Thus, I thought all the city names (for 20 years) should be kept in a separate table as reference data. Upon insertion of a new country/city, the mapping between plate numbers and city names should be made using this reference table.

Unfortunately, I haven't been able to map this. I tried mapping a single entity to multiple tables. (using join-table) However, I realized this would not work because what I would like to have is a reference table, with no coupling with any objects at all, with all the city names in it.

My insertion behaviour should be like this : If city name does not exist on the reference table, it should be added. If it exists, nothing should be added to the reference table. The dynamic city table should be updated with the given population, year and plate number data.

My retrieval behaviour should be like this : The specific city instance I would like to have should be compiled using the dynamic and reference table.

I don't want to change my classes other than adding a simple integer plate number in it. Furthermore, I am using HBM XML mapping.

I would be very grateful if you could help me solve my issue. Furthermore, I would also be very happy if you could share any links related to handling redundancies in Hibernate, which I have not been able to find.

Thanks in advance.