trevorpan
3 months ago

States, Cities, Zip Codes and Counties database tables (best table design for populating dropdowns)

Posted 3 months ago by trevorpan

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`

Please sign in or create an account to participate in this conversation.