Building Your Startup With PHP: Feature Requirements and Database Design

Final product image — What You'll Be Creating

This is the second part in the Building Your Startup with PHP series, in which I lead you through development from concept to launch of my startup, Meeting Planner. In this part, I'll provide an overview of the feature goals and requirements, and then lead you through the initial database design and migration.

Because we're not going to see a lot of functionality come to life immediately, this episode may seem a bit dry, but it's essential to lay the groundwork for all that will follow. If you haven't designed Active Record database migrations and used Yii's scaffolding application, Gii, you'll probably learn a lot.

Feature Requirements

Before describing the specifics of the Meeting Planner database, I'll walk you through the high-level features that Meeting Planner will offer.

Planning Meetings

Allow users to create a meeting request and invite another person (just one person for our minimum viable product).
Allow users to suggest and choose places.
Allow users to suggest and choose days and times.
Allow users to send notes to each other during the meeting planning process.
Suggest places suitable for meetings close to the user.
Record a log (or history) of all additions and changes to meetings.

Supporting Places

Allow users to add favorite places they like to meet at regularly.
Allow users to add their own places for meetings such as home and office.
Suggest places near to each participant or equidistant, based in part on popularity.

Supporting Users

Maintain lists of all the user's meetings in process, confirmed and past.
Allow users to provide their contact information such as phone numbers and Skype addresses for online conferences.
Allow users to turn off unwanted emails, i.e. unsubscribe.
Require users authenticate their emails before delivering invitations.
Make it easy to stop emails related to unwanted invitations, e.g. spam.

Making Things Easier and Faster

Allow users to create templates that make scheduling common meetings easier, with suggested days and times and favorite places, e.g. I'd like to schedule a morning coffee with so and so at my usual preferred place, day of the week, and start time
Send emails with meeting changes, with URL links to commands for making changes, e.g. canceling or requesting a change to the place, day or time; authenticate users through verification codes in these links.
Send meeting reminders a day before with contact details and directions.

Earning Revenue

Allow advertisers, e.g. restaurants, coffeeshops, and rental office spaces to advertise their places.

While the above is not an exhaustive list of features, it gives you a clear idea of what we need the database schema to support.

Installing the Meeting Planner Repository

To begin setting up your development environment for Meeting Planner, you can use my guide Programming with Yii2: Getting Started; follow the instructions to install Composer.

All of the Meeting Planner tutorials will be tagged in our free, open source Github repository. So, for this part of the tutorial series, you can install the basic Meeting Planner framework from here.

For Meeting Planner, I've installed Yii2's advanced application template which provides a slightly more robust architecture for complex applications, e.g. different applications for front-end (end user) and back-end (administrative) access.

To get started with the code, you'll need to clone the repository, check out the tagged release for this part of the tutorial, run initialize, and ask Composer to update files:

cd ~/Sites
git clone git@github.com:newscloud/mp.git
cd ~/Sites/mp
git checkout p2 
sudo -s php init
sudo composer update

I'm using MAMP in my local development environment. So, I need to point my preferred front-end localhost URL to ~/Sites/mp/frontend/web:

1	cd /Applications/MAMP/htdocs
2	ln -s ~/Sites/mp/frontend/web/ /Applications/MAMP/htdocs/mp

If you navigate your browser to http://localhost:8888/mp, you should see something like this:

Then, you'll need to create a database in MySQL and configure the settings in \environments\dev\common\main-local.php:

<?php
return [
    'components' => [
        'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => 'mysql:host=localhost;dbname=your-db',
            'username' => 'your-username',
            'password' => 'your-pwd',
            'charset' => 'utf8',
        ],

Before we can dive further into running the migrations, I'd like to walk you through the preliminary database design.

Designing the Database Schema

Because I'm in the early stages of building the code, I'm attempting to do a thorough job of laying out the database; however, it's likely that the design may need to change or evolve as I move forward.

Yii's Active Record migrations make it relatively easy to programmatically create databases in different environments, e.g. local and production, and to incrementally evolve them. You can learn more about Yii's Active Record here.

The User Table

The first migration builds the user table, and it's included in Yii's advanced application template—see /mp/console/migrations/m130524_201442_init.php.

This migration tells Yii to create a new SQL table with the fields needed for a user table shown below:

<?php

use yii\db\Schema;
use yii\db\Migration;

class m130524_201442_init extends Migration
{
    public function up()
    {
        $tableOptions = null;
        if ($this->db->driverName === 'mysql') {
            // http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
        }

        $this->createTable('{{%user}}', [
            'id' => Schema::TYPE_BIGPK,
            'friendly_name' => Schema::TYPE_STRING . ' NOT NULL',
            'username' => Schema::TYPE_STRING . ' NOT NULL',
            'auth_key' => Schema::TYPE_STRING . '(32) NOT NULL',
            'password_hash' => Schema::TYPE_STRING . ' NOT NULL',
            'password_reset_token' => Schema::TYPE_STRING,
            'email' => Schema::TYPE_STRING . ' NOT NULL',
            'role' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 10',

            'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 10',
            'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
            'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
        ], $tableOptions);
    }

    public function down()
    {
        $this->dropTable('{{%user}}');
    }
}

You can run the first migration as shown below:

1	cd ~/Sites/mp
2	./yii migrate/up 1

You should see something like this:

Jeffs-Mac-mini:mp Jeff$ ./yii migrate/up 1
Yii Migration Tool (based on Yii v2.0.0)

Creating migration history table "migration"...done.
Total 1 out of 15 new migrations to be applied:
    m130524_201442_init

Apply the above migration? (yes|no) [no]:yes
*** applying m130524_201442_init
    > create table {{%user}} ... done (time: 0.068s)
*** applied m130524_201442_init (time: 0.071s)


Migrated up successfully.

Yii provides built-in web support for common operations such as signup, login, logout, and more. This functionality and this table will provide the base of support for our initial authentication capabilities. We may later extend it in various ways, for example supporting Twitter or Google OAuth for authentication.

With Active Record migrations, you can also migrate backwards. This can be especially helpful during development. For example, migrating down will drop the User table:

Jeffs-Mac-mini:mp Jeff$ ./yii migrate/down 1
Yii Migration Tool (based on Yii v2.0.0)

Total 1 migration to be reverted:
    m130524_201442_init

Revert the above migration? (yes|no) [no]:yes
*** reverting m130524_201442_init
    > drop table {{%user}} ... done (time: 0.001s)
*** reverted m130524_201442_init (time: 0.070s)


Migrated down successfully.

If you need to adjust your table design, you can do so and then migrate back up.

The Meeting Table

The Meeting schema and all the tables associated with meetings will be incredibly important to the functionality of our application.

Here's the base schema for a Meeting:

 $this->createTable('{{%meeting}}', [
            'id' => Schema::TYPE_PK,
            'owner_id' => Schema::TYPE_BIGINT.' NOT NULL',
            'meeting_type' => Schema::TYPE_SMALLINT.' NOT NULL DEFAULT 0',
            'message' => Schema::TYPE_TEXT.' NOT NULL DEFAULT ""',
            'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
            'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
            'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
        ], $tableOptions);

The base of a meeting consists of an owner, a type of meeting designator, an invitation message, a status field, and the standard created and updated time fields.

With Active Record, Yii can help us automatically create relations between tables. In the meeting table, we'll create a relation that every Meeting has one Owner in the User table. We do this in the migration by creating a foreign key connecting the Meeting -> Owner_ID to the User->ID.

1	$this->addForeignKey('fk_meeting_owner', '{{%meeting}}', 'owner_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');

We'll also need to drop the foreign key in the down migration:

    public function down()
    {
       	$this->dropForeignKey('fk_meeting_owner', '{{%meeting}}');
        $this->dropTable('{{%meeting}}');
    }

Bear with me as I outline more of the schema before we jump into Yii's automated scaffolding system, Gii.

You can see all the migrations in /mp/console/migrations folder:

We'll review most of them below.

The Place Table

Places are also a critical component in Meeting Planner, because they are the places that everyone will meet. They are indexed by geolocation and referenced in Google Places.

Here's the schema for a Place:

$tableOptions = null;
      if ($this->db->driverName === 'mysql') {
          $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
      }

      $this->createTable('{{%place}}', [
          'id' => Schema::TYPE_PK,
          'name' => Schema::TYPE_STRING.' NOT NULL',          
          'place_type' => Schema::TYPE_SMALLINT.' NOT NULL DEFAULT 0',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'google_place_id' => Schema::TYPE_STRING.' NOT NULL', // e.g. google places id
          'created_by' => Schema::TYPE_BIGINT.' NOT NULL',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_place_created_by', '{{%place}}', 'created_by', '{{%user}}', 'id', 'CASCADE', 'CASCADE');

Places consist of a name, place_type, status, created_at, and updated_at. But they also include a google_place_id to relate them to the Google Places directory.

Note, there isn't any geolocation associated with a Place in this table. That's because the MySQL InnoDB engine doesn't support spatial indexes. So I've created a secondary table using the MyISAM table for Places' geolocation coordinates. It's the Place_GPS table:

class m141025_213611_create_place_gps_table extends Migration
{
  public function up()
  {
      $tableOptions = null;
      if ($this->db->driverName === 'mysql') {
          $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=MyISAM';
      }

      $this->createTable('{{%place_gps}}', [
          'id' => Schema::TYPE_PK,
          'place_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'gps'=>'POINT NOT NULL',
      ], $tableOptions);
      $this->execute('create spatial index place_gps_gps on '.'{{%place_gps}}(gps);');
      $this->addForeignKey('fk_place_gps','{{%place_gps}}' , 'place_id', '{{%place}}', 'id', 'CASCADE', 'CASCADE');
  }

Note that it's related back to the Place table by place_id. The location of places is simply a GPS coordinate—or MySQL POINT.

The Participant Table

Meeting participants are stored in a join table called Participant. They join the Meeting table by meeting_id and the User table by participant_id. If we wish to have more than one meeting participant per meeting, this table will allow for that in the future.

class m141025_215701_create_participant_table extends Migration
{
  public function up()
  {
      $tableOptions = null;
      if ($this->db->driverName === 'mysql') {
          $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE=InnoDB';
      }

      $this->createTable('{{%participant}}', [
          'id' => Schema::TYPE_PK,
          'meeting_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'participant_id' => Schema::TYPE_BIGINT.' NOT NULL',
          'invited_by' => Schema::TYPE_BIGINT.' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_participant_meeting', '{{%participant}}', 'meeting_id', '{{%meeting}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_participant_participant', '{{%participant}}', 'participant_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_participant_invited_by', '{{%participant}}', 'invited_by', '{{%user}}', 'id', 'CASCADE', 'CASCADE');      
  }

Other Related Meeting Tables

There are several other tables that help define our meeting options for planning.

The Meeting Time Table

This table contains all of the suggested meeting times (and dates) by start, which is a timestamp. Suggested_by shows who suggested the time. And status determines whether the time is selected for the meeting.

      $this->createTable('{{%meeting_time}}', [
          'id' => Schema::TYPE_PK,
          'meeting_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'start' => Schema::TYPE_INTEGER.' NOT NULL',
          'suggested_by' => Schema::TYPE_BIGINT.' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_meeting_time_meeting', '{{%meeting_time}}', 'meeting_id', '{{%meeting}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_participant_suggested_by', '{{%meeting_time}}', 'suggested_by', '{{%user}}', 'id', 'CASCADE', 'CASCADE');      

The Meeting Place Table

This table shows which Places have been suggested for a meeting:

$this->createTable('{{%meeting_place}}', [
          'id' => Schema::TYPE_PK,
          'meeting_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'place_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'suggested_by' => Schema::TYPE_BIGINT.' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_meeting_place_meeting', '{{%meeting_place}}', 'meeting_id', '{{%meeting}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_meeting_place_place', '{{%meeting_place}}', 'place_id', '{{%place}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_meeting_suggested_by', '{{%meeting_place}}', 'suggested_by', '{{%user}}', 'id', 'CASCADE', 'CASCADE');     

The Meeting Log Table

This table records the history of all additions and changes for a specific meeting. Every action taken during meeting scheduling is recorded to provide a chronological history of events related to a meeting. It will help users see a record of all changes to their meetings over time, and it will also likely help us in development with debugging.

$this->createTable('{{%meeting_log}}', [
          'id' => Schema::TYPE_PK,
          'meeting_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'action' => Schema::TYPE_INTEGER.' NOT NULL',
          'actor_id' => Schema::TYPE_BIGINT.' NOT NULL',
          'item_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'extra_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_meeting_log_meeting', '{{%meeting_log}}', 'meeting_id', '{{%meeting}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_meeting_log_actor', '{{%meeting_log}}', 'actor_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');

The Meeting Note Table

Users are able to send short notes back and forth when they make changes to meetings. This table records those notes.

$this->createTable('{{%meeting_note}}', [
          'id' => Schema::TYPE_PK,
          'meeting_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'posted_by' => Schema::TYPE_BIGINT.' NOT NULL DEFAULT 0',
          'note' => Schema::TYPE_TEXT.' NOT NULL DEFAULT ""',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_meeting_note_meeting', '{{%meeting_note}}', 'meeting_id', '{{%meeting}}', 'id', 'CASCADE', 'CASCADE');
      $this->addForeignKey('fk_meeting_note_posted_by', '{{%meeting_note}}', 'posted_by', '{{%user}}', 'id', 'CASCADE', 'CASCADE');     

Other Related User Tables

There are several tables to expand the User definition.

The Friend Table

This is an index table listing the friends of each user. It also tracks whether they are favorite friends and the number of meetings they've had. This may be helpful for simplifying the scheduling experience, e.g. showing favorite or frequent friends first.

$this->createTable('{{%friend}}', [
          'id' => Schema::TYPE_PK,
          'user_id' => Schema::TYPE_BIGINT.' NOT NULL',
          'friend_id' => Schema::TYPE_BIGINT.' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'number_meetings' => Schema::TYPE_INTEGER . ' NOT NULL DEFAULT 0',
          'is_favorite' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_friend_user_id', '{{%friend}}', 'user_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');     
      $this->addForeignKey('fk_friend_friend_id', '{{%friend}}', 'friend_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');     

The User Place Table

This is an index table of places the user prefers to meet at or has met at in the past. We'll track favorite places and the number of meetings held by that user here. The is_special field will indicate that a place is the user's own home, office, or meeting spot.

$this->createTable('{{%user_place}}', [
          'id' => Schema::TYPE_PK,
          'user_id' => Schema::TYPE_BIGINT.' NOT NULL',
          'place_id' => Schema::TYPE_INTEGER.' NOT NULL',
          'is_favorite' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'number_meetings' => Schema::TYPE_INTEGER . ' NOT NULL DEFAULT 0',
          'is_special' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'note' => Schema::TYPE_STRING . ' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_user_place_user', '{{%user_place}}', 'user_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');     
      $this->addForeignKey('fk_user_place_place', '{{%user_place}}', 'place_id', '{{%place}}', 'id', 'CASCADE', 'CASCADE');     

The User Contact Table

This table provides contact information for a specific user, e.g. telephone numbers, Skype addresses, and any notes associated with contacting the user in those places.

 $this->createTable('{{%user_contact}}', [
          'id' => Schema::TYPE_PK,
          'user_id' => Schema::TYPE_BIGINT.' NOT NULL',
          'contact_type' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'info' => Schema::TYPE_STRING . ' NOT NULL',
          'details' => Schema::TYPE_TEXT . ' NOT NULL',
          'status' => Schema::TYPE_SMALLINT . ' NOT NULL DEFAULT 0',
          'created_at' => Schema::TYPE_INTEGER . ' NOT NULL',
          'updated_at' => Schema::TYPE_INTEGER . ' NOT NULL',
      ], $tableOptions);
      $this->addForeignKey('fk_user_contact_user', '{{%user_contact}}', 'user_id', '{{%user}}', 'id', 'CASCADE', 'CASCADE');     

For simplicity, I'll skip over the Meeting Template schema at this time. And I have not yet designed features related to revenue. The primary reason for this is that I have a lot on my plate at the moment to get the core feature set running and complete the first few episodes of the tutorial series. Yet, this is a teachable moment. It's a good example of an entrepreneur with limited resources focusing on core functionality without "realizing" that revenue generation is also a core feature. Because I believe I can bootstrap Meeting Planner initially without revenue, it's a compromise I'm able to make at this time.

Running the Database Migrations

Now that you have a bit more background on our database schema and Active Record migrations, let's run the rest of them:

1	cd ~/Sites/mp
2	./yii migrate/up all

You should see something like this:

Yii Migration Tool (based on Yii v2.0.0)

Total 14 new migrations to be applied:
    m141025_212656_create_meeting_table
    m141025_213610_create_place_table
    m141025_213611_create_place_gps_table
    m141025_215701_create_participant_table
    m141025_215833_create_meeting_time_table
    m141025_220016_create_meeting_place_table
    m141025_220133_create_meeting_log_table
    m141025_220524_create_friend_table
    m141025_220923_create_user_place_table
    m141025_221627_create_meeting_note_table
    m141025_221902_create_user_contact_table
    m141025_222213_create_template_table
    m141025_222431_create_template_time_table
    m141025_222531_create_template_place_table

Apply the above migrations? (yes|no) [no]:yes
*** applying m141025_212656_create_meeting_table
    > create table {{%meeting}} ... done (time: 0.124s)
    > add foreign key fk_meeting_owner: {{%meeting}} (owner_id) references {{%user}} (id) ... done (time: 0.307s)
*** applied m141025_212656_create_meeting_table (time: 0.434s)

*** applying m141025_213610_create_place_table
    > create table {{%place}} ... done (time: 0.091s)
    > add foreign key fk_place_created_by: {{%place}} (created_by) references {{%user}} (id) ... done (time: 0.114s)
*** applied m141025_213610_create_place_table (time: 0.206s)

*** applying m141025_213611_create_place_gps_table
    > create table {{%place_gps}} ... done (time: 0.120s)
    > execute SQL: create spatial index place_gps_gps on {{%place_gps}}(gps); ... done (time: 0.114s)
    > add foreign key fk_place_gps: {{%place_gps}} (place_id) references {{%place}} (id) ... done (time: 0.112s)
*** applied m141025_213611_create_place_gps_table (time: 0.347s)

*** applying m141025_215701_create_participant_table
    > create table {{%participant}} ... done (time: 0.100s)
    > add foreign key fk_participant_meeting: {{%participant}} (meeting_id) references {{%meeting}} (id) ... done (time: 0.138s)
    > add foreign key fk_participant_participant: {{%participant}} (participant_id) references {{%user}} (id) ... done (time: 0.112s)
    > add foreign key fk_participant_invited_by: {{%participant}} (invited_by) references {{%user}} (id) ... done (time: 0.149s)
*** applied m141025_215701_create_participant_table (time: 0.500s)

*** applying m141025_215833_create_meeting_time_table
    > create table {{%meeting_time}} ... done (time: 0.142s)
    > add foreign key fk_meeting_time_meeting: {{%meeting_time}} (meeting_id) references {{%meeting}} (id) ... done (time: 0.148s)
    > add foreign key fk_participant_suggested_by: {{%meeting_time}} (suggested_by) references {{%user}} (id) ... done (time: 0.122s)
*** applied m141025_215833_create_meeting_time_table (time: 0.413s)

*** applying m141025_220016_create_meeting_place_table
    > create table {{%meeting_place}} ... done (time: 0.120s)
    > add foreign key fk_meeting_place_meeting: {{%meeting_place}} (meeting_id) references {{%meeting}} (id) ... done (time: 0.125s)
    > add foreign key fk_meeting_place_place: {{%meeting_place}} (place_id) references {{%place}} (id) ... done (time: 0.135s)
    > add foreign key fk_meeting_suggested_by: {{%meeting_place}} (suggested_by) references {{%user}} (id) ... done (time: 0.137s)
*** applied m141025_220016_create_meeting_place_table (time: 0.518s)

*** applying m141025_220133_create_meeting_log_table
    > create table {{%meeting_log}} ... done (time: 0.109s)
    > add foreign key fk_meeting_log_meeting: {{%meeting_log}} (meeting_id) references {{%meeting}} (id) ... done (time: 0.126s)
    > add foreign key fk_meeting_log_actor: {{%meeting_log}} (actor_id) references {{%user}} (id) ... done (time: 0.113s)
*** applied m141025_220133_create_meeting_log_table (time: 0.348s)

*** applying m141025_220524_create_friend_table
    > create table {{%friend}} ... done (time: 0.109s)
    > add foreign key fk_friend_user_id: {{%friend}} (user_id) references {{%user}} (id) ... done (time: 0.125s)
    > add foreign key fk_friend_friend_id: {{%friend}} (friend_id) references {{%user}} (id) ... done (time: 0.102s)
*** applied m141025_220524_create_friend_table (time: 0.337s)

*** applying m141025_220923_create_user_place_table
    > create table {{%user_place}} ... done (time: 0.109s)
    > add foreign key fk_user_place_user: {{%user_place}} (user_id) references {{%user}} (id) ... done (time: 0.137s)
    > add foreign key fk_user_place_place: {{%user_place}} (place_id) references {{%place}} (id) ... done (time: 0.114s)
*** applied m141025_220923_create_user_place_table (time: 0.360s)

*** applying m141025_221627_create_meeting_note_table
    > create table {{%meeting_note}} ... done (time: 0.109s)
    > add foreign key fk_meeting_note_meeting: {{%meeting_note}} (meeting_id) references {{%meeting}} (id) ... done (time: 0.125s)
    > add foreign key fk_meeting_note_posted_by: {{%meeting_note}} (posted_by) references {{%user}} (id) ... done (time: 0.101s)
*** applied m141025_221627_create_meeting_note_table (time: 0.337s)

*** applying m141025_221902_create_user_contact_table
    > create table {{%user_contact}} ... done (time: 0.098s)
    > add foreign key fk_user_contact_user: {{%user_contact}} (user_id) references {{%user}} (id) ... done (time: 0.125s)
*** applied m141025_221902_create_user_contact_table (time: 0.225s)

*** applying m141025_222213_create_template_table
    > create table {{%template}} ... done (time: 0.108s)
    > add foreign key fk_template_owner: {{%template}} (owner_id) references {{%user}} (id) ... done (time: 0.171s)
*** applied m141025_222213_create_template_table (time: 0.281s)

*** applying m141025_222431_create_template_time_table
    > create table {{%template_time}} ... done (time: 0.111s)
    > add foreign key fk_template_time_template: {{%template_time}} (template_id) references {{%template}} (id) ... done (time: 0.114s)
*** applied m141025_222431_create_template_time_table (time: 0.226s)

*** applying m141025_222531_create_template_place_table
    > create table {{%template_place}} ... done (time: 0.099s)
    > add foreign key fk_template_place_template: {{%template_place}} (template_id) references {{%template}} (id) ... done (time: 0.103s)
    > add foreign key fk_template_place_place: {{%template_place}} (place_id) references {{%place}} (id) ... done (time: 0.101s)
*** applied m141025_222531_create_template_place_table (time: 0.304s)

Migrated up successfully.

Similarly, when we install Meeting Planner in production, we'll use migrations to build out the initial database there as well. There's no need to export and import SQL files that might break depending on the variety of versions we might be using across environments.

Registering the Administrative User

Before we go further, you need to register yourself as the administrative user. Click the signup link in the toolbar and simply sign up for the application.

If you're successful, when you return to the home page, you'll see the toolbar indicates your logged in status.

These forms and application logic are all included in Yii's advanced application template.

Using Yii's Gii to Build Scaffolding

Now we can build the scaffolding to support the Model View Controller code for common Create, Read, Update and Delete operations (CRUD).

We'll use Gii, Yii's amazing automated code generator, to build a lot of our basic framework code. The name may be silly but it's incredibly powerful and central to Yii development. We'll start with Meetings and Places.

Using Gii

Point your browser at http://localhost:8888/mp/gii. You should see this:

Generating Models

When building with Gii, you generally start with the Model Generator for each table. Before you can use the Model Generator, you have to have run your migrations to create the tables in the database, as we did above. Gii uses the SQL table definitions to generate code for your Model.

Meeting Planner Yii2 Gii Model Generator

Let's use the Model Generator to generate model code for the Meeting table. The code will already have been generated in your Github repository, but feel free to run through these exercises again. Gii will preview and optionally overwrite the code for you.

Fill in the Model Generator as follows for the Meeting model:

Meeting Planner Yii2 Gii Model Generator Meetings

Then, generate the Place model:

Meeting Planner Yii2 Gii Model Generator Place Table

Gii is pretty amazing—building on our table definition, it generates a ton of logic.

In the /mp/frontend/models/Meeting.php model, you'll see auto-generated attribute labels:

    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'owner_id' => 'Owner ID',
            'meeting_type' => 'Meeting Type',
            'message' => 'Message',
            'status' => 'Status',
            'created_at' => 'Created At',
            'updated_at' => 'Updated At',
        ];
    }

It generates field validation rules for forms:

    public function rules()
    {
        return [
            [['owner_id', 'message', 'created_at', 'updated_at'], 'required'],
            [['owner_id', 'meeting_type', 'status', 'created_at', 'updated_at'], 'integer'],
            [['message'], 'string']
        ];
    }

And it generates database relations—here are a few for example:

/* @property User $owner
 * @property MeetingLog[] $meetingLogs
 * @property MeetingNote[] $meetingNotes
 * @property MeetingPlace[] $meetingPlaces
 * @property MeetingTime[] $meetingTimes
 * @property Participant[] $participants
*/
/**
     * @return \yii\db\ActiveQuery
     */
    public function getMeetingLogs()
    {
        return $this->hasMany(MeetingLog::className(), ['meeting_id' => 'id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getMeetingNotes()
    {
        return $this->hasMany(MeetingNote::className(), ['meeting_id' => 'id']);
    }

    /**
     * @return \yii\db\ActiveQuery
     */
    public function getMeetingPlaces()
    {
        return $this->hasMany(MeetingPlace::className(), ['meeting_id' => 'id']);
    }

Generating CRUD

Now, we can use the CRUD generator to build the code for create, read, update and delete operations.

Visit the CRUD Generator and create it for Meetings. Note that front end is the Yii application which the user will see.

When you click Preview, you should see something like this:

Meeting Planner Yii2 Gii Model Generator Overwrite

When you click Generate, you should see the following results:

Meeting Planner Yii2 Gii Model Generator Success

Next, repeat the process above for Places.

Already, you can actually browse Meetings and Places at our site to see the code that's been generated in action. Point your browser at http://localhost:8888/mp/meeting. It should look something like this:

If you registered your account, you should be able to create a meeting. Notice that Gii doesn't know the difference between fields which our code should manage and those which are to be provided by users. We'll clean these up in coming tutorials. For now, you'll need to enter integers for owner_id (use 1—that's the first signed in user), meeting_type, status, created_at, and updated_at:

After creating a couple of meetings, the Meeting index page will look like this:

Meeting Planner Meeting Controller with Data

Combining the power of Gii and Yii makes building web applications much faster than it would otherwise be. It's pretty amazing that with just a database table structure and a block of migration code, we can be steps away from working controllers and forms, built responsively with Bootstrap.

What's Next?

I hope you've found the database and Gii walk-through interesting. The next article in this series will feature on building out functionality around Places. It will describe how to use Google Places, Google Maps and HTML5 geolocation to build out the features Meeting Planner needs. If you'd like a sneak peek at these topics, I wrote a related tutorial, How to Use Zillow Neighborhood Maps and HTML5 Geolocation.

Please feel free add your questions and comments below; I generally participate in the discussions. You can also reach me on Twitter @reifman or email me directly.