CODEWITHSUNDEEP
mysqldatabasedatabase-designrelational-databasedatabase-schema
Jeremie
Jeremie

Reputation: 584

About associations with multiple tables (Database)

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!

J

Database modeling

Upvotes: 3

Views: 676

Answers (3)

Jeremie
Jeremie

Reputation: 584

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?

Thanks!

Upvotes: 0

Hammerite
Hammerite

Reputation: 22340

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.)

Upvotes: 1

eggyal
eggyal

Reputation: 125855

As you say, you could use an intermediate table:

CREATE TABLE regions (
region_id  INT NOT NULL AUTO_INCREMENT,
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.

Upvotes: 1

Related Questions