Hi there`
Have been working on a filter bar with html selects and came across the idea of dynamically populated dropdowns.
I've sketched out a number of ideas on how to structure the tables. It seems like a states table makes sense, not worried about countries yet, and right now I've got a ArizonaCitiesTable. I thought this would help against duplicate city names like Las Vegas, NM and Las Vegas (well of course Nevada!):
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateArizonaCitiesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('arizona_cities', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('city');
$table->string('county');
$table->unsignedMediumInteger('zipcode')->length(5);
$table->timestamps();
});
}
Does this seem legit, or would you break out zipcodes, county?
These values then populate a jobs table.
And one other deal, with this kind of setup, do you have to create a sql file to upload on the production server to populate the values?
Thank you`
Are you doing this a a research/learning process? If not, it might be better to consume a 3rd party service that is based off of Zip codes. Quick search found this: https://github.com/antonioribeiro/zipcode
It's a bit outdated, but the concepts are solid. This way you remove the overhead/management of creating and maintaining the data itself.
I would not create a table for each state's cities but rather use a foreign key to connect them.
Something like
mysql> desc states;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| short_name | varchar(10) | NO | UNI | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+----------------+------------------+------+-----+---------+----------------+
mysql> desc cities;
+----------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| name | varchar(255) | NO | | NULL | |
| state_id | int(10)unsigned | NO | UNI | NULL | |
| password | varchar(255) | NO | | NULL | |
| created_at | timestamp | YES | | NULL | |
| updated_at | timestamp | YES | | NULL | |
+----------------+------------------+------+-----+---------+----------------+
Or you could use the state's acronym together with the name to create the primary key.
@GN0RT0N - Hi, ok cool I just checked that one out. Thank you`
Also looked into the post office and UPS api but felt they were a bit much for me to handle now.
My site is quite a ways away from needing other countries; it's not a learning process for the sake of learning, but to get AZ working. All about the minimum viable product . . . in entrepreneur parlance.
Just wanting to populate like this: https://www.youtube.com/watch?v=c7-HkztGahM
@TRAY2 - ok, I'll give that a shot and post the results. Since these tables are static, other than a new zipcode every now and then, would each state have a sql file? I guess that would be a mysql source /file command? Have/How do you do that on a production server?
Check database seeders here
@TRAY2 - if I'm not mistaken seeding is for testing? Are you suggesting that's a way to populate these via the command line on the production server?
below is the path to solution..
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class State extends Model
{
protected $fillable = ['state'];
// hasMany
public function cities() //$job->user
{
return $this->hasMany(City::class);
}
}
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class City extends Model
{
protected $fillable = ['city', 'county', 'zipcode'];
// belongsTo
public function state() //$job->user
{
return $this->belongsTo(State::class);
}
}
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCitiesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('cities', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('city');
$table->string('county');
$table->unsignedMediumInteger('zipcode')->length(5);
$table->string('statecode')->length(2);
$table->timestamps();
$table->engine = 'InnoDB';
});
}
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateStatesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('states', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('state');
$table->string('statecode')->length(2);
$table->foreign('statecode')->references('statecode')->on('cities');
$table->timestamps();
$table->engine = 'InnoDB';
});
}
After trying for quite a while, I found the column types need to be the same. See the string did not allow, as one of the columns needed to increment or something like that (can't remember exactly)
https://coderwall.com/p/o73fbq/creating-foreign-key-in-laravel-migrations
in Laravel 5.8+bigIncrements must align with unsignedBigInteger(). this was a real pain, but:
https://stackoverflow.com/questions/55745137/general-error-1215-cannot-add-foreign-key-constraint-laravel-5-mysql/55746533#55746533
Another thing that sucked was the time of the migration, in this case I had to change the states migration a second prior to cities otherwise it wouldn't be found!
$table->engine = 'InnoDB'; some posts I found said you needed to declare this, but the solution below does not require it.
Following is the solution that allowed migrations. Now that this worked, does it appear to you the foreign key is set up properly?
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateStatesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('states', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('state');
$table->timestamps();
});
}
<?php
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateCitiesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('cities', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('city');
$table->string('county');
$table->unsignedMediumInteger('zipcode')->length(5);
$table->unsignedBigInteger('statecode');
$table->foreign('statecode')->references('id')->on('states');
$table->timestamps();
});
}
so after solving the above I find this:
Apache Junction is a city in Maricopa and Pinal counties in the state of Arizona.
Does this suggest having a counties table with a many to many with cities and a belongsTo with state? Or would you store two values in the county column? This stuff is tedious.
Seeding can be used both for testing and for seeding the production data.
As for the counties table, Yes you should probably use a pivot table.
Yes this step can be quite tedious but it's like building the foundation of a house. A bad foundation will give you costly problems later on.
When I design a database I use tests to drive out the design. So the first design might be a single table.
A books table might look like this from the start
id
title
author_name
released
format
genre
isbn
blurb
And author can have many books
id
title
author_id
released
format
genre
isbn
blurb
authors table
id
last_name
first_name
And then a book can have many authors
books table
id
title
released
format
genre
isbn
blurb
Authors table
id
last_name
first_name
Author_books table
id
author_id
book_id
So the trick is to try to keep it DRY.
@TRAY2 - Ok, I think I have it.
class CreateStatesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('states', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('state');
});
}
class CreateCitiesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('cities', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('city');
$table->unsignedBigInteger('state_id');
$table->foreign('state_id')->references('id')->on('states');
$table->timestamps();
});
}
class CreateCountiesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('counties', function (Blueprint $table) {
$table->bigIncrements('id');
$table->string('county');
$table->timestamps();
});
}
class CreateZipCodesTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('zipcodes', function (Blueprint $table) {
$table->bigIncrements('id');
$table->unsignedMediumInteger('zipcode')->length(5);
$table->timestamps();
});
}
class CreateCityCountyPivotTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
//
Schema::create('city_county', function(Blueprint $table)
{
$table->primary(['city_id', 'county_id']);
$table->unsignedBigInteger('city_id')->unsigned()->index();
$table->foreign('city_id')->references('id')->on('cities');
$table->unsignedBigInteger('county_id')->unsigned()->index();
$table->foreign('county_id')->references('id')->on('counties');
});
}
apologies on the formatting, I wish these code blocks would allow wrapping or overflow
class CreateCityZipcodePivotTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('city_zipcode', function (Blueprint $table) {
$table->primary(['city_id', 'zipcode_id']);
$table->unsignedBigInteger('city_id')->unsigned()->index();
$table->foreign('city_id')->references('id')->on('cities');
$table->unsignedBigInteger('zipcode_id')->unsigned()->index();
$table->foreign('zipcode_id')->references('id')->on('zipcodes');
});
}
class CreateStateZipcodePivotTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('state_zipcode', function (Blueprint $table) {
$table->primary(['state_id', 'zipcode_id']);
$table->unsignedBigInteger('state_id')->unsigned()->index();
$table->foreign('state_id')->references('id')->on('states');
$table->unsignedBigInteger('zipcode_id')->unsigned()->index();
$table->foreign('zipcode_id')->references('id')->on('zipcodes');
});
}
Here, I believe the model ZipCode belongsToMany states. Does that seem right, as zipcodes cross state lines? Or is hasmany states more appropriate?
class ZipCode extends Model
{
protected $fillable = ['zipcode'];
// belongsToMany
public function state()
{
return $this->belongsToMany(State::class);
}
// hasMany
public function cities()
{
return $this->hasMany(City::class);
}
public function counties()
{
return $this->hasMany(County::class);
}
}
class State extends Model
{
protected $fillable = ['state'];
// hasMany
public function cities()
{
return $this->hasMany(City::class);
}
public function zipcodes()
{
return $this->hasMany(ZipCode::class);
}
public function counties()
{
return $this->hasMany(County::class);
}
}
class City extends Model
{
protected $fillable = ['city'];
// belongsTo
public function state()
{
return $this->belongsTo(State::class);
}
// hasMany
public function counties()
{
return $this->hasMany(County::class);
}
public function zipcodes()
{
return $this->hasMany(ZipCode::class);
}
}
Putting in the data will be a nightmare, I can now see why @gn0rt0n offered up a package.
Does having zipcodes out of sorting order matter much? e.g. AZ is in the 85000 range. Should that data be placed in the database sequentially? Thank you `
It looks like it would work just fine at first glance.
The sorting order of the zip codes or any table for that matter is/should be handled by the database.
$zipCodes = ZipCode:orderBy('zipcode', 'ASC')->get();
So you don't need to worry about which order to store rows in the database.
@TRAY2 - Ok, thank you very much. I tend to have a orderly desire for all the records to be sequential but am going to have to let go!
Please or to participate in this conversation.