Laravel Eloquent A good way to make relationship between country state and city table

Question

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?

Answer 1

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