Best practices for Laravel Database Migrations

If you have a solid foundation in SQL and creating relational database models, you don’t need this article.   That said, having worked professionally with Laravel migrations and SQL in general here and there, I see several common areas that are overlooked by Laravel developers from time to time.

Best practices for SQL are best practices for Eloquent

Just because it works, doesn’t mean it’s optimal.  A poorly designed relational database will slow down your Website.  Use SQL best practices.  Read about it.  This is a good place to start:  https://www.upwork.com/hiring/data/tips-and-best-practices-for-sql-programmers/

Normalize your tables

Each field should be dependent on 1 unique primary key, when possible.  That’s it in a nutshell, but normalization involves quit a bit more.  Read about it.  https://www.essentialsql.com/get-ready-to-learn-sql-database-normalization-explained-in-simple-english/

Define your tables with exactness.  Do not do this:

 $table->string('my_alphnum_index');

Doing this will create a default field length of 255 characters, which will not only compete with the rest of your table if you are creating a lot of columns, but it will also consume more resources when your SQL driver scans the index.  If your index has at max 6 characters, do this:

 $table->string('my_alphanum_index', 6);

Use foreign keys where appropriate.

You have to create an index on the parent and child key, and create a foreign key in the child table. This is done is the database/migrations folder. Here is an example:

public function up()
{
Schema::create('my_items', function (Blueprint $table) {

    $table->increments('id');
    $table->string('vendor_id', 11);
    $table->string('manufacturer_id', 11);
    $table->timestamps();

    $table->index('vendor_id');
    $table->foreign('vendor_id')->references('id')->on('vendors');
    });
}