Geek asks and answers

About Multiple Table Associations (Database)

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

I know there is a trick when placing tables between them, but I searched and still haven't found this template.

Thank you so much!

J

Database modeling

+3
source share
3 answers

As you say, you can use a staging 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)
)

regions (, , ), NULL.

+1

: place. country, state city place. item place.

. , place . item . ( , .)

+1

, , .

"item" , , , :

, (: , ).

"items", "", , ( "", "", "" ).

(, "", "" ..), , , "" .

?

!

0

More articles:


All Articles