Take the 2-minute tour ×

I want to associate and item to either a city, a state or a country in my database. However, I would like to have only one field in the 'items' table to make the link to either the city, the state or the country to which it is associated (instead of having the three entries 'city_id', 'state_id' and 'country_id'). See the image:

I know there's a trick on putting tables in between, but I searched and still haven't found that pattern.

Thanks a lot!


Database modeling

share|improve this question
I don't think you can do this in a normalized way, but I'd love to be wrong... –  rid Apr 24 '12 at 16:10
add comment

3 Answers

As you say, you could use an intermediate table:

CREATE TABLE regions (
country_id INT NOT NULL,
state_id   INT,
city_id    INT,
PRIMARY KEY (region_id),
UNIQUE  KEY (country_id, state_id, city_id)
CONSTRAINT FOREIGN KEY (country_id)           REFERENCES countries (id),
CONSTRAINT FOREIGN KEY (country_id, state_id) REFERENCES states (country_id, id),
CONSTRAINT FOREIGN KEY (  state_id,  city_id) REFERENCES cities (  state_id, id)

You'd need to keep this regions table updated with every possible (country, state, city) combination including where state or city are NULL.

share|improve this answer
While this is normalized, it does nothing but add a new JOIN to every query... But you're right, it does seem to be what the OP was asking for. –  rid Apr 24 '12 at 16:24
I'm still not sure if this is exactly what I need, but I will check it out. Thanks! –  Jeremie Apr 24 '12 at 16:33
@Jeremie: How did it go? Is it what you're after? If not, what issues are you having with it? –  eggyal Apr 27 '12 at 7:09
@eggyal: Still thinking about it. I have a similar problem in another part of my DB, so I'm still thinking about how I should do this. –  Jeremie Apr 27 '12 at 15:48
@Jeremie: Any update? FYI, you should accept answers if they work for you (it's why we do it!). Better yet, if the answer has been helpful to you, you might consider upvoting it too. –  eggyal May 1 '12 at 19:42
show 1 more comment

Have you considered adding a fifth table: place. Every country, state and city is a place. Every item is associated to a place.

This is still not normalised. You have to do the work of making sure that the sets of place values in the three tables are disjoint. You also have more work to do in joining item to the appropriate table. (Essentially you trade one set of difficulties for another.)

share|improve this answer
add comment

Here's another idea, tell me what you think.

Since the fields in "item" will be optional, and that the relations between countries, states & cities is already defined, maybe I could try that way:

The primary keys of the countries, states & cities will be a unique ID through all those three tables (ex.: no state ID will have the same primary key as any city or country entry).

Then I could put this unique ID in the "items" table as well as the "type" which would give the table where to look for it ('cities', 'states', 'countries').

Since there could be some changes (ex.: 'regions', 'continents', etc.), then maybe it would be better to simply "add" a new table and type instead of regenerating the associative table.

Does it make sense?


share|improve this answer
add comment

Your Answer


