This is part 2 of the Laravel 5 & Dingo API series, in which we’re building an API to receive orders from 3rd parties and ship them to recipients. Last time we covered setting up the environment and making a test API call using Dingo API. This time around, we’ll be looking at what the database architecture could be like, and how we create it with migrations and setting up models with Eloquent ORM in Laravel to allow the API to access our database.

Creating a Database

As with most applications you’ll ever write or interact with, the database is key and it’s design and organisation will be wholly dependent on the application you’re crafting. Right now, I’m going to look at an order API. Lets ponder the database architecture for a moment.

What information do we need to store in order to achieve an order API?

• Who’s ordered something (eg the “customer” that we’re going to bill, possibly including billing information)
• Where are we shipping it to
• What items have been ordered
  • size
  • colour
  • brand
  • type
  • design
• How many items have been ordered
• What status an order is (has it been dispatched)

We’re going to have an order, which can consist of many items. Each item can be of a particular variant (think size, colour or brand). This gives us this as a rough database architecture:

One Order has many Items

One Item belongs to one Order

One Item has one Variant

One Variant can be in many Order Items

We could also think about an order having a purchaser and a recipient. It may be too complex for the initial version of this API, but just so you can optionally add this my basic logic is:

One Order belongs to one User

One User has many Orders

One Order can be dispatched to one Recipient

One Recipient can receive many Orders

In my case I’m going to create the following objects:

[table caption=”Orders” ]
Name, Description
ID, our unique identifier for this order.
order_ref, this is the customer reference for the order
recipient, this is going to be an array of recipient address data.
shipping_method, the shipping method to be used for this order
recipient_id, the FK to the recipients table so we know who to post the order to.
[/table]

[table caption=”Items” ]
Name, Description
ID, our unique identifier for this item.
item_ref, this is the customer reference for the item
quantity, number if this item to be sent
variant_id, the Foreign key used to find the variant information for the order
[/table]

[table caption=”Variants” ]
Name, Description
ID, our unique identifier for this item. Usually an SKU
size, size of the variant
brand, brand of the variant
type, type of variant, eg hoodie, t-shirt
colour, the colour of the garment
design, the design on the garment (if any)
[/table]

(just for reference, if the tutorial were to be expanded, the recipient’s table would be included too. This provides better data normalisation than just listing the recipient of an order inside the order table.)

[table caption=”Recipients” ]
Name, Description
ID, our unique identifier for this order.
name, name of the recipient for shipping
address_1, first line of their address
address_2, second line of their address
city, their town or city
region, the county or state
country_code, a 2 digit ISO standard country code for international orders
post_code, their post code
phone, their phone number which will be given to the shipping company
email, optionally their email address for marketing
[/table]

Migrations

Laravel uses a feature called Migrations to create database schemas. This is fantastic when you’re working with multiple developers as you can architect the database in flat files and “migrate” changes each time you pull down a new version of the code base. It also makes it easier than writing out all of the database tables in SQL or using a tool like MySQL Workbench to create them. We’re going to use the power of the migrations in Laravel to create our API table layout.

From the command line, lets create a migration for each table we’re going to use

php artisan make:migration create_table_orders
php artisan make:migration create_table_items
php artisan make:migration create_table_variants

You’ll have three new files created in your project folder in database/migrations. These files will be prefixed with a date time stamp, for example php artisan make:migration create_table_orders. You’ll also note that there are some preexiting migrations for users and password resets. You’re welcome to poke around these and see what they’re doing before moving on.

Lets start with the orders migration. Open it up in Atom so we can add information about our table into it. What you’ll be presented with is a class containing an up function and a down function, which equate to create and delete. When we run migrations it creates the table, if we reset it’ll delete the table in our database. Lets add our table elements:

class CreateTableOrders extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('orders', function (Blueprint $table) {
            // This is an auto incrementing primary key
            $table->increments('id');
            // This is a string or integer provided by the customer
            $table->string('order_ref');
            // This would link to our recipients table and be a foreign key, but we're not implementing it in our tutorial
            $table->integer('recipient_id');
            // This is a string indicating what shipping method we're going to use. Technically this should be split into it's own table for shipping methods, but for now it'll do.
            $table->string('shipping_method');
            // Timestamps are really useful for data monitoring. They let you see the created_at and updated_at times for the record.
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('orders');
    }
}

Our items table:

class CreateTableItems extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('items', function (Blueprint $table) {
            // This is an auto incrementing primary key
            $table->increments('id');
            // This is a string or integer provided by the customer
            $table->string('item_ref');
            // How many of this item are in this order?
            $table->integer('quantity');
            // This is a Forigin Key to the variants table, allowing us to know which of our item variants we want to provide in the order.
            $table->string('variant_id');
            // Timestamps are really useful for data monitoring. They let you see the created_at and updated_at times for the record.
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('items');
    }
}

And finally our Variants table:

class CreateTableVariants extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('variants', function (Blueprint $table) {
            // This is an auto incrementing primary key
            $table->increments('id');
            // OPTIONALLY:
            // an item can have an SKU. This is a unique id and is great for a primary key, but there are ongoing debates in the MySQL community over this being a good or bad idea. Auto incrementing fields are easier to index after all.
            //
            // If you wanted to include an SKU you could do it by using the following:
            //$table->string('sku')->unique();
            // This is the garment size
            $table->string('size');
            // This is the garment brand
            $table->string('brand');
            // This is the garment type
            $table->string('type');
            // This is the garment colour
            $table->string('colour');
            // This is the garment design
            $table->string('design');
            // Timestamps are really useful for data monitoring. They let you see the created_at and updated_at times for the record.
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::drop('variants');
    }
}

Now, before we can begin playing with migrations, you need to have your database connection setup. If you’re happy that it is then lets go back to the command line and run:

php artisan migrate

You should get a message similar to the following in response:

Migration table created successfully.
Migrated: 2014_10_12_000000_create_users_table
Migrated: 2014_10_12_100000_create_password_resets_table
Migrated: 2016_03_06_174944_create_table_items
Migrated: 2016_03_06_174944_create_table_orders
Migrated: 2016_03_06_174945_create_table_variants

If you got anything else, including an error, then you need to take a look at what it’s telling you and correct it before continuing.

Optionally at this point you can open up a database editor and check it’s all there as you expected it to be. Since I’m calling this another milestone in the project, I’m also going to commit my changes.

git add -A
git commit -m "initial database migrations created"

The migration tool is quite powerful and will let you roll back migrations, or even reset them (delete the tables – remember those down functions in our migration files – that’s when these are called) or refresh, which resets then re-migrates the tables. Take a look at the Laravel docs on migrations for more information.

Seeders

A quick note on seeders. We can actually create a few records in the database using a seeder in Laravel, which is great for testing things like API get requests. Seeders are created on the command line using php artisan make:seeder. It might be worth adding some for our tables, but right now I won’t. You can always research if you’re interested in the subject.

Creating our Models

Now that the database has been created, we can begin to look at making some API calls to retrieve, add, delete and edit data to them. Most of our work will take place in the App folder form now on.

In order to talk to the database in Laravel we will be using a model. Once again, if you want details of MVC frameworks in general, or what models do in Laravel I encourage you to do your own research. I can’t cover everything here! What we will do is drop back to the command line and create some models for our new tables. I follow the convention of models being named as plural – so “order” is “orders”. This is preference and you don’t need to follow it if you don’t want to.

php artisan make:model Orders
# Model created successfully.
php artisan make:model Items
# Model created successfully.
php artisan make:model Variants
# Model created successfully.

You’ll see the app directory has files named the same as those listed on the command line arguments.Laravel uses Eloquent to mange database queries and relationships. Lets open them up one at a time and define some of these relationships between the tables.

The orders table needs to be related to items. Since one order has many items, we can reflect it in Laravel’s order model like so:

class Orders extends Model
{
    public function items()
    {
        return $this->hasMany('App\Items');
    }
}

Similarly we need to create the reverse of the relationship in the items model:

class Items extends Model
{

    public function order()
    {
        return $this->belongsTo('App\Orders');
    }

}

But, an item can also have a variant, so lets add another relationship to the same items model:

class Items extends Model
{

    public function order()
    {
        return $this->belongsTo('App\Orders');
    }

    public function variant()
    {
        return $this->belongsTo('App\Variants');
    }

}

Take note, that because there’s one variant, and one order for one item, I’ve used singulars as the function names. On the orders model I’ve used a plural “items” as there are many items in an order. This is preference, and adjust as you see fit.

Finally lets load up the variants model and add the items relationship:

class Variants extends Model
{
    public function items()
    {
        return $this->hasMany('App\Items');
    }
}

We’ll see how these relationships benefit us later. For now, lets take a look at further work on the models. A model will use a table of the same name by default, find out more at the Laravel docs website.

I’m also going to allow fillable fields using mass assignment. To do this in each of the models create an array of variables that you’ll allow to be fillable.

The orders class now looks like this:

class Orders extends Model
{
    protected $fillable = ['order_ref', 'recipient_id', 'shipping_method'];
    
    public function items()
    {
        return $this->hasMany('App\Items');
    }
}

Lets add fillable column names to the other models.

Items:

protected $fillable = ['item_ref', 'quantity', 'variant_id'];

Variants:

protected $fillable = ['size', 'brand', 'colour', 'type', 'design'];

That’s us done playing with models right now. Save all that and lets add it to git.

git add -A 
git commit -m "created models for orders, items and variants with fillable fields defined and relationships created"