Hi all.

I'm looking for a strategy for how to map a common scenario I see in a database at work. Note that it doesn't actually use City and State, I'm just using them as an example.

Table State:


Table CityType:


Mapping the State table could be simple, but mapping the City table might be more difficult. I cannot use the "CityID" column as a primary key, since it has duplicates (the string key "other" or "springfield" is used in multiple states).

There will be cases where other entities use these tables to reference states and cities, and sometimes it will just reference states. But, assume it will NEVER just reference cities, so we don't have to worry about that.

If I went with using two entities, the State entity is simple, but the City entity would have to have a composite key. I tried this one out first, but it seems if I have both the state and city entity in another entity (say, "Address"), then I'll have problems trying to save an "address" entity. Using this entity:


Address:


and trying to save with a state entity (using StateID) and a city entity (using StateID and CityID) will give me this error:



The SQL shows that the "State" column name is being put into the insert statement twice. I'm not sure if this is work-aroundable, although I haven't explored the avenue much.

I don't think using one entity will work, because then the entity will only be able to look at one table or the other (unless I were to use "join" in the mapping, which I haven't done before, so not sure if I should look down that road).

Finally, there's the idea of making two simple entities, and a third "wrapper" entity that can be used to make sure that the city matches a state.

Thanks in advanced!

If class City has a refernce to clss State, you could use a many-to-one mapping between City and State where many cities map to one State. State would know nothing about a City's relationship to itself. Of course, with this you would have other tables reference City and City would imply State.

If you don't want this want of relationship then I would create an object that associates the two so that I could use it to get all the Cities in a State and vice-versa. In this case, you might have a class like CityState (not to be confused the likes of Rome, ancient Athens and possibly Liechtenstein).

In either case, you could use either approach to associate cities to states. If your other entities have references to them both then I would probably use the latter approach so that State and City classes don't have unecessary references. CityState would be used to insert/update cities in the system, but would rarely be used otherwise.