php - 千城万户的关系，什么才是正确的做法？

Question

在一个应用程序中，用户可以选择他们想要的位置/城市。城市列表将采用树状结构，带有如下复选框:

[x]Country
-[x]State 1
--[x]City 1
--[x]City 2
--[x]City 100
-[X]State 2
--[x]City 1
--[x]City 2
--[x]City 100
[x]Country 2

……

现在，如果用户选择国家/地区，那么他将拥有所有州和城市。如果选择一个或多个州并且那些州、城市和国家也被存储用于该用户。用户可以选择一个或多个国家。用户也可以取消选中任何一个所需的城市或州。

您建议如何存储和检索数据。我正在使用 MySql 和 Php。查找表是否理想？或者我可以在文本类型列中以 json 格式存储所有位置 ID 和存储吗？

谢谢!

Answer 1

How would you suggest to store and retrieve data. I'm using MySql and Php. Will a look-up table be ideal?

要存储国家、州和城市，您应该使用规范化的数据库架构。国家有州。国家有城市。您需要联结表。

OR can I grand all locations id kand store in json format in a text type column?

没有。那不是正常的形式。而且会产生很多问题。您无法轻松执行 CRUD 操作。

数据库模式

create table countires(name varchar(100) primary key);
create table sates(name varchar(100) primary key, country varchar(100), foreign key `country` references `countries`(`name`)); 
create table cities(name varchar(100) primary key, state varchar(100), foreign key `state` references `states`(`name`)); 

现在您可以对这些表运行任何不同类型的查询。假设用户选择了城市(city1、city2)、国家/地区 cntry1 和州 stt2。

查询

查找用户选择的所有城市。

SELECT ct.name 
FROM   cities AS ct 
       JOIN states AS st 
         ON ( st.name = city.state ) 
       JOIN countries AS cn 
         ON ( cn.name = st.country ) 
WHERE  ct.name IN ( 'city1', 'city2' ) 
        OR cn.name = 'cntry1' 
        OR st.name = 'stt2'; 

查找用户选择的所有状态。

SELECT st.name 
FROM   states AS st 
       JOIN countries AS cn 
         ON ( cn.name = st.country ) 
WHERE   OR cn.name = 'cntry1' 
        OR st.name = 'stt2'; 

更新 1

how to maintain its relation with user?

你需要Junction tables .只需创建其中的 3 个即可。

create table users(name varchar(100) primary key);
CREATE TABLE user_countries 
  ( 
     user    VARCHAR(100), 
     country VARCHAR(100), 
     PRIMARY KEY (`user`, `country`), 
     FOREIGN KEY (`user`) REFERENCES `users`(`name`)
     FOREIGN KEY (`counry`) REFERENCES `countries`(`name`)    
  ); 
CREATE TABLE user_states 
  ( 
     user    VARCHAR(100), 
     state   VARCHAR(100), 
     PRIMARY KEY (`user`, `state`), 
     FOREIGN KEY (`user`) REFERENCES `users`(`name`) 
     FOREIGN KEY (`state`) REFERENCES `states`(`name`)    
  ); 
CREATE TABLE user_cities 
  ( 
     user    VARCHAR(100), 
     city    VARCHAR(100), 
     PRIMARY KEY (`user`, `city`), 
     FOREIGN KEY (`user`) REFERENCES `users`(`name`) 
     FOREIGN KEY (`city`) REFERENCES `cities`(`name`)    
  );