CODEWITHSUNDEEP
phpmysqldatabase
aryan kumar
aryan kumar

Reputation: 151

Thousands of Cities and Users relationship, what's the right way?

In one application, user are allowed to select their desired locations/cities. The city list will be in a tree structure with checkbox like: 

[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

......

Now, if user select country then all he'll have all state and cities. If selects one ore more states and those state, cities and country too be be stored for that user. User can select one or more country. Also user can uncheck any one desired city or state.

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?

Thanks!

Upvotes: 1

Views: 810

Answers (3)

Shiplu Mokaddim
Shiplu Mokaddim

Reputation: 57690

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

To store country, state and cities you should use normalized database schema. Country has states. States has cities. You need Junction tables.

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

No. that would not be a normal form. And it'll create a lot of problem. You can not perform CRUD operations easily.

Database schema

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`));

Now you can run any different type of queries on these tables. Say user have selected city (city1, city2), country cntry1 and a state stt2.

Queries

Find all the cities that users have selected.

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';

Find all the states that users have selected.

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';

Update 1

how to maintain its relation with user?

You need Junction tables. Just create 3 of them.

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`)    
  );

Upvotes: 3

Allan
Allan

Reputation: 17429

I would recommend storing only the selected values and deriving the rest. Yes, it'll be somewhat more painful to retrieve the data, but it'll have a couple significant upsides:

It'll continue to be accurate when your source data changes: Imagine you had this application running when the Soviet Union broke up. You convert you "USSR" entry to read "Russia" and convert a bunch of "States" to countries. With your model, this would cause massive amounts of user data to be changed. However, with a derived model, this becomes much simpler. Anyone with a city selected is automatically fixed when the city is assigned to the proper country. A bunch of people with a state selected may suddenly find they have a country selected instead, but the cities under that level are automatically fixed.

It'll simplify your create, update and delete operations: Instead of writing hundreds or thousands of rows, every change affects exactly one row.

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71422

I would say it depends on how you are going to need to access the stored data. If you are only going to look up the data based on the user who submitted it, then there is probably no reason that you couldn't store the serialized tree data (i.e JSON, PHP serialize, etc.) in a database record associated with the user. Incidentally this would also be a good use case for a NoSQL data store.

If however you need to be able to, say lookup all users with a specific country, state, or city selected, then you will need to store you data such that it is queryable in this fashion. That might mean separate tables for countries, state, and cities with table to relate the users to each, or it could simply mean a single jurisdiction table that allows different types of jurisdictions within it along with a table relating users to jurisdictions. How you approach the schema will likely depend on how differently you need to treat countries, states, and cities from each other (i.e does each jurisdiction type need to have drastically different attributes).

Upvotes: 0

Related Questions