Create database tables for Laravel e-commerce application

Now that we have our requirements for our Laravel e-commerce application, we are ready to get started with actually developing the application. (Finally!)

We will use the powerful Laravel “migrations” feature to create our database tables and relationships. Migrations may seem like an odd name, but essentially it allows us to have some version control for the changes that we make to our database over the lifecycle of the project. Likewise, it is helpful for allowing others to take our source code and get their database up and running quickly, even if they are using a completely different database engine, since the migrations files are independent of the database platform that you are using.

In general, I like to use the migrations tool to create a template file and then manually customize the details to fit my preferences. In particular, I prefer to include the table name as part of the primary key column in the tables, whereas Laravel simply names the column ‘id’. Likewise, if you have some “boilerplate” columns that you like to include in all of your tables, using the template helps to ensure consistency of these column names.

  1. To begin, we need to create a database for our application. Since we are using the LAMP stack, we’ll create the database in MySQL. You can create the database in the Terminal (see below) or, if you prefer, you can use other tools, such as phpMyAdmin.

    For this example, we’ll name the database ‘laravel’ and simply use the MySQL ‘root’ user account. Likewise, I’m assuming that the MySQL ‘root’ account does not have a password. Obviously, this is VERY insecure, but it is fine for your local development environment. In a production scenario, you would want to create a specific user and grant that user limited permissions to the database.

    To log into MySQL, run:

    mysql -u root
    

    If the MySQL ‘root’ user has a password, you can simply append -p to the command and you’ll be prompted to enter the password.

    At the mysql> prompt, we will create our database and add a new user, with a password of ‘password’, with full privileges to the database.

    CREATE DATABASE laravel DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
    CREATE USER 'laravel_crud'@'localhost' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON laravel.* TO 'laravel_crud'@'localhost';
    

    After each command, MySQL should respond with Query OK and some other relevant information. In the last statement, laravel.* limits the command to the laravel database, but to all tables in that database.

    We chose the user name ‘laravel_crud’ to indicate that this user will have full privileges on this database. ‘CRUD’ is an acronym for ‘Create-Read-Update-Delete’, which are all of the typical operations on database tables.

    That’s all we need for the database for now! Simply log out of MySQL by entering exit;.

  2. Now that we have our database, we need to configure Laravel to access the database. In Aptana (or another text editor), open the app/config/database.php file and locate the 'connections' array. In the 'connections' array, update the 'mysql' element array:

    'mysql' => array(
       'driver'    => 'mysql',
       'host'      => 'localhost',
       'database'  => 'laravel',
       'username'  => 'laravel_crud',
       'password'  => 'password',
       'charset'   => 'utf8',
       'collation' => 'utf8_unicode_ci',
       'prefix'    => '',
    ),
    

    You can quickly confirm that the configuration is valid by displaying the site in your web browser (e.g., http://projects.local/laravel/). As long as the configuration is correct, the page should display correctly. If there is a configuration error, you’ll get a nice Laravel error message.

  3. Laravel’s built-in migration functionality will simply create a “shell” of the class for us. We will supplement this with the excellent Laravel 4 Generators package by Jeffrey Way. To add this (or any third-party) package, in the Terminal, navigate to the root of the Laravel project (e.g., ~/projects/laravel) and run:

    composer require "way/generators":"2.*" --prefer-dist
    

    This will add the package to your composer.json file. Now we just need to run a Composer update to install the new package and its dependencies, if any.

    composer update
    

    To activate the new Generators package, open the app/config/app.php file and add this line to the end of the providers array:

    'Way\Generators\GeneratorsServiceProvider',
    

    If you like, you can also add an alias, such as Generator to the aliases array. Having an alias makes it simple to reference the classes by the short name, instead of the fully-qualified name.

  4. Based on our requirements, we need the following tables (and associated Laravel models):

    • account
    • product
    • order
    • order_item

    Let’s start by creating a migration for the account table. We’ll then use this migration class as a template for the other tables.

    First, we need to create the migrations table in our database. This table simply keeps track of the various changes that we’ve made to the actual tables used by our application. To create the table, run:

    php artisan migrate:install
    

    To generate (using the Generators package which we added above) the migration for the account table, in the Terminal, from the project root directory, run:

    php artisan generate:migration create_account_table --fields="email_addr:string, password:string"
    

    This will create a migration file in the app/database/migrations with the name YYYY_MM_DD_HHMMSS_CreateAccountTable.php where YYYY is the 4-digit year, MM is the month, DD is the day and HHMMSS is the hour/minute/second that the migration was created. (Note that the HHMMSS is the UTC time, based on your system’s time zone setting.) For example, 2014_08_05_203018_CreateAccountTable.php. Open this file in your editor (Aptana, etc.). The generated file will look like:

    <?php
    
    use Illuminate\Database\Migrations\Migration;
    use Illuminate\Database\Schema\Blueprint;
    
    class CreateAccountTable extends Migration {
    
    	/**
    	 * Run the migrations.
    	 *
    	 * @return void
    	 */
    	public function up()
    	{
    		Schema::create('account', function(Blueprint $table)
    		{
    			$table->increments('id');
    			$table->string('email_addr');
    			$table->string('password');
    			$table->timestamps();
    		});
    	}
    
    
    	/**
    	 * Reverse the migrations.
    	 *
    	 * @return void
    	 */
    	public function down()
    	{
    		Schema::drop('account');
    	}
    
    }
    

    As you can see, this class essentially consists of the up() method, which creates the table in the database, and the down() method, which drops the table. When you apply the migration (see below), the up() method will create the account table in the database.

    We want to make a few customizations to the class for our specific needs. The only changes that are required are to the up(). Change this method as follows.

    	public function up()
    	{
    		Schema::create('account', function(Blueprint $table)
    		{
    			$table->increments('account_id');
    			$table->string('email_addr', 50);
    			$table->string('password', 30);
    			$table->boolean('admin_ind')->default(0);			
    			$table->string('address1', 50)->nullable();
    			$table->string('address2', 50)->nullable();
    			$table->string('city', 20)->nullable();
    			$table->string('state', 2)->nullable();
    			$table->string('postal_code', 20)->nullable();
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    

    Now that we have a basic template for our migrations, we can use it for the other tables that we need for our database. Here are the details for each of the other classes. Again, we’ll only show the up() method, but you need to update the class name and the down() method for each, appropriately. Likewise, each class should be saved in it’s own file.

    product table

    	public function up()
    	{
    		Schema::create('product', function(Blueprint $table)
    		{
    			$table->increments('product_id');
    			$table->string('product_code', 20);
    			$table->string('product_name', 50);
    			$table->string('product_description');
    			$table->string('media_type', 10);
    			$table->integer('unit_qty');
    			$table->float('unit_price');
    			$table->string('download_directory', 100)->nullable();
    			$table->boolean('available_ind')->default(1);
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    

    order table

    	public function up()
    	{
    		Schema::create('order', function(Blueprint $table)
    		{
    			$table->increments('order_id');
    			$table->integer('account_id')->unsigned();  // FK to account table
    			$table->string('order_status');
    			$table->float('order_gross')->nullable();
    			$table->float('order_net')->nullable();
    			$table->float('discount')->nullable();
    			$table->float('transaction_fee')->nullable();
    			$table->string('payment_gateway_txn_id', 30)->nullable();
    			$table->string('payment_gateway_order_id', 30)->nullable();
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    

    order_item table

    	public function up()
    	{
    		Schema::create('order_item', function(Blueprint $table)
    		{
    			$table->increments('order_item_id');
    			$table->integer('order_id')->unsigned();  // FK to order table
    			$table->integer('product_id')->unsigned();  // FK to product table
    			$table->integer('qty');
    			$table->float('extended_price');
    			$table->dateTime('created');
    			$table->integer('created_by_account_id');
    			$table->dateTime('last_modified');
    			$table->integer('last_modified_by_account_id');
    		});
    	}
    
  5. Once we have all of the migrations classes created for our tables, we are ready to apply the migrations to create the tables in our database. Actually, running the migrations is very simple:

    php artisan migrate
    

    Laravel will list the migrations that are executed in the terminal. Likewise, you can check the migrations table in the database to see them. The batch column in the migrations table gets incremented for each run.

    If you need to revert any updates made via the migration, you run the migrate:rollback command which reverts only the last migration. If you want to start over completely, use the migrate:reset command. In addition, migrate:refresh will do a reset and then load the current migrations.

    To get a list of all of the migrations commands run php artisan list command. Most of the migrations commands start with ‘migrate’.

    To learn more about Laravel migrations, I highly recommend the article “Managing Databases with Migrations”.

That’s it for this topic. Next time, we’ll start creating the Laravel models corresponding to our database tables.

Advertisements

One thought on “Create database tables for Laravel e-commerce application

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s