-1

I have 3 tables with schema like below

countries (rows: 250)
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI |         | auto_increment |
| name       | varchar(255)        | NO   |     |         |                |
| code       | varchar(255)        | NO   | UNI |         |                |country code
| phone_code | varchar(255)        | NO   |     |         |                |
| region     | varchar(255)        | NO   |     |         |                |
| subregion  | varchar(255)        | NO   |     |         |                |
| created_at | timestamp           | YES  |     |         |                |
| updated_at | timestamp           | YES  |     |         |                |
| deleted_at | timestamp           | YES  |     |         |                |
+------------+---------------------+------+-----+---------+----------------+
states (rows: 4866)
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI |         | auto_increment |
| name        | varchar(255)        | NO   |     |         |                |
| country_code| varchar(255)        | NO   | MUL |         |                | this is country code
| state_code  | varchar(255)        | YES  |     |         |                |
| lat         | varchar(255)        | YES  |     |         |                |
| lon         | varchar(255)        | YES  |     |         |                |
| created_at  | timestamp           | YES  |     |         |                |
| updated_at  | timestamp           | YES  |     |         |                |
| deleted_at  | timestamp           | YES  |     |         |                |
+-------------+---------------------+------+-----+---------+----------------+    
cities (rows: 146068)
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| id         | bigint(20) unsigned | NO   | PRI |         | auto_increment |
| name       | varchar(255)        | NO   |     |         |                |
| lat        | varchar(255)        | YES  |     |         |                |
| lng        | varchar(255)        | YES  |     |         |                |
| population | varchar(255)        | YES  |     |         |                |
| state_code | varchar(255)        | NO   |     |         |                |state code not unique
| created_at | timestamp           | YES  |     |         |                |
| updated_at | timestamp           | YES  |     |         |                |
| deleted_at | timestamp           | YES  |     |         |                |
+------------+---------------------+------+-----+---------+----------------+

I am using quickadminpanel to generate these CRUD but the main issue is i imported these from a csv files git link for csv and csvimport trait like https://pastebin.com/G9z8Rjf1

is there any way i can build relationship between these three tables using country:code and state:country_code relationship and state:state_code and city:state_code relationship because i cannot add states (rows: 4866) and cities (rows: 146068) manually

so how can i form relationship using models or any better way or any better trait for making relationship?

Rohit Mahajan
  • 16
  • 6
  • Why wouldnt you use the primary key as a reference key to another table? – JEJ May 04 '21 at 10:31
  • You can use the primary key of country table is the foreign keys of other tables,, and if you want to retrieve also the country code with each record then you can simply use `with ()` function – JEJ May 04 '21 at 10:34
  • thats what i want to do but to do that the `code` and `country_code` column should match and make a relationship using a model or using db it doesent matter – Rohit Mahajan May 04 '21 at 10:36
  • how do i match it with different table i got good example https://stackoverflow.com/a/57358462/15121609 but here he has search query but i have a column in both table any way to get that ? – Rohit Mahajan May 04 '21 at 10:40
  • You can simply just change the primary key from id to code – JEJ May 04 '21 at 10:46
  • Ok lets say i have country_code something like `select * from countries , states where countries.code = "IN" AND countries.code = states.country_code` this is the raw sql i come up with dont know if its wrong or not but it should be like this i think – Rohit Mahajan May 04 '21 at 10:47
  • for country i can change primary key to code since its unique but i cant say for state and city since state code is repeated between countries but not in a country – Rohit Mahajan May 04 '21 at 10:48
  • yeah thats correct.. you can set code as primary in countries table and coutry_code as foreign key in both state and city(repeatable) and state_code In state will be unique in state tables well as city code will be unique in city table..Have you got the point? right>? – JEJ May 04 '21 at 10:53

1 Answers1

0

Just Change The Primary Keys of each table to code,state_code,city_code Respectively

NB:change multiple to unique the state_code in states table and city_code in cities table

And In your Models change The Relationship like

/* return $this->hasMany(Model::class, 'foreign_key', 'local_key');*

return $this->hasMany(State::class, 'country_code', 'code');

And

/// return $this->belongsTo(Model::class, 'foreign_key', 'owner_key');///

return $this->belongsTo(Country::class, 'code', 'country_code');

then You can access all data like normal..

JEJ
  • 814
  • 5
  • 21
  • i got an issue i cannot create a primary key of `state_code` column in both table since its not unique any way for that ? it just says plz select primary key or unique key – Rohit Mahajan May 04 '21 at 11:04
  • U are storing States names in state table right so? state code will be unique in states table ...(it will be the foreign key in city table because a state have many cities..). a state doesn't have many state code.. just like a key.. it will be unique. – JEJ May 04 '21 at 11:08
  • change the state_code and city_code to unique in state table and city table respectively – JEJ May 04 '21 at 11:10
  • state code is not unique because its repeated because of different country using same state code :( – Rohit Mahajan May 04 '21 at 11:11
  • in that table state code MH for Maharashtra ( which is in India) is also code for County Meath which is in Ireland and many more like this example with same state code – Rohit Mahajan May 04 '21 at 11:14
  • @RohitMahajan Okay. There is 2 ways 1. Have to add an extra column to every table to relate this data. 2. Just keep state_code as Unique.. i prefer 2nd method since its related to real-life example( a pincode,a state code,a country code) cant be repeated. its only belongs to one state/city/country – JEJ May 04 '21 at 11:15
  • it seems country code is not unique between country but is unique for a country https://ibb.co/QFg8r0m check this so if thats the case the option 2 is out – Rohit Mahajan May 04 '21 at 11:22
  • https://github.com/dr5hn/countries-states-cities-database/blob/master/csv/states.csv search for ur state and then use same state code see if it matches for my case it matches atleast 5 countries using same code :( – Rohit Mahajan May 04 '21 at 11:23
  • i will try to make a table with relation between each country , state and city let me try that option 1 use different table to relate the data – Rohit Mahajan May 04 '21 at 11:28
  • Yeah just add and extra column and set its default value as same as index value of country table index value(value of id) – JEJ May 04 '21 at 11:44
  • i created a pivot table for the same it should work now thanks – Rohit Mahajan May 04 '21 at 12:00