Design a startup’s database using PHP and outline functional requirements

This is the second part of the “Build Your Startup with PHP” series, in which I will walk you through the entire development process of my startup Meeting Planner from concept to launch. In this section, I outline the functional goals and requirements, and then walk you through the initial database design and migration.

This episode may seem a little dry since we won't see many features coming into effect right away, but it's crucial to lay the groundwork for everything to come. If you haven't designed an Active Record database migration and used Gii, a scaffolding application for Yii, you'll probably learn a lot.

Feature Requirements

Before describing the specifics of the Meeting Planner database, I will walk you through the advanced features that Meeting Planner will provide.

Planning Meeting

• Allow users to create meeting requests and invite others (our minimum viable product only invites one person).
• Allows users to suggest and select locations.
• Allows the user to suggest and select a date and time.
• Allow users to send notes to each other during the meeting planning process.
• Suggest suitable locations for meetings near users.
• A log (or history) of all meeting additions and changes.

Support locations

• Allows users to add favorite places where they like to meet up regularly.
• Allows users to add their own meeting locations, such as home and office.
The
• section suggests locations that are close or equidistant to each participant based on popularity.

Support users

• Maintain a list of all ongoing, confirmed and past user meetings.
• Allow users to provide contact information such as phone number and Skype address for online meetings.
• Allow users to close unwanted emails, i.e. unsubscribe.
• Require users to verify the identity of their email before sending invitations.
• Easily block emails related to unwanted invitations, such as spam.

Make things easier and faster

• Allows users to create templates to make it easier to schedule common meetings with suggested dates and times and favorite locations, e.g. I want to schedule a drink with someone at my favorite location, day of the week, and start time Morning coffee
• Send an email containing meeting changes and include URL links for commands to make the changes, such as canceling or requesting a change of location, date, or time; users are authenticated via verification codes in these links.
• Send meeting reminders one day in advance with contact information and directions.

earn income

• Allows advertisers such as restaurants, coffee shops and rental office spaces to promote their establishments.

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.

Install Meeting Planner Repository

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

All 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 installed Yii2's Advanced Application Template, which provides a slightly more powerful architecture for complex applications, such as different applications for front-end (end-user) and back-end (administration) access .

To start working with the code, you need to clone the repository, view the tagged version of this part of the tutorial, run initialization, and ask Composer to update the 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. Therefore, I need to point my preferred frontend localhost URL to ~/Sites/mp/frontend/web:

cd /Applications/MAMP/htdocs
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 need to create the database in MySQL and place it in \environment\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 dive any further into running the migration, I want to walk you through a preliminary database design.

Design database architecture

Because I am in the early stages of building the code, I am trying to be thorough with the layout of the database; however, the design may need to change or evolve as I move forward.

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

用户表

第一次迁移构建了用户表，它包含在 Yii 的高级应用程序模板中 - 请参阅 /mp/console/migrations/m130524_201442_init.php。

此迁移告诉 Yii 创建一个新的 SQL 表，其中包含用户表所需的字段，如下所示：

<?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}}');
    }
}

您可以运行第一次迁移，如下所示：

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

您应该看到类似这样的内容：

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 为注册、登录、注销等常见操作提供内置 Web 支持。此功能和此表将为我们的初始身份验证功能提供支持基础。我们稍后可能会以各种方式对其进行扩展，例如支持 Twitter 或 Google OAuth 进行身份验证。

通过 Active Record 迁移，您还可以向后迁移。这在开发过程中特别有帮助。例如向下迁移会删除User表：

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.

如果您需要调整表格设计，可以先进行调整，然后再迁移回来。

会议桌

会议架构以及与会议相关的所有表格对于我们应用程序的功能非常重要。

这是会议的基本架构：

 $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);

会议的基础由所有者、会议指示符类型、邀请消息、状态字段以及标准创建和更新时间字段组成。

通过 Active Record，Yii 可以帮助我们自动创建表之间的关系。在会议表中，我们将创建一个关系，即每个会议在用户表中都有一个所有者。我们在迁移中通过创建一个外键将会议 -> Owner_ID 连接到用户 -> ID 来完成此操作。

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

我们还需要在向下迁移中删除外键：

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

在我们进入 Yii 的自动化脚手架系统 Gii 之前，请耐心等待我概述更多架构。

您可以在 /mp/console/migrations 文件夹 中看到所有迁移：

我们将在下面回顾其中的大部分内容。

餐位表

地点也是 Meeting Planner 中的一个重要组成部分，因为它们是每个人都会见面的地点。它们按地理位置编入索引，并在 Google 地方信息中引用。

这是地点的架构：

$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');

地点由 name、place_type、status、created_at 和 更新_at。但它们还包含 google_place_id 将它们与 Google Places 目录关联起来。

请注意，没有任何与此表中的地点关联的地理位置。这是因为 MySQL InnoDB 引擎不支持空间索引。因此，我使用 MyISAM 表创建了一个辅助表来存储地点的地理位置坐标。这是 Place_GPS 表：

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');
  }

请注意，它通过 place_id 与 Place 表相关。地点的位置只是 GPS 坐标，或 MySQL POINT。

参与者表

会议参与者存储在名为“参与者”的连接表中。他们通过 meeting_id 加入会议表，并通过 participant_id 加入用户表。如果我们希望每次会议有多个会议参与者，此表将在将来实现这一点。

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');      
  }

其他相关会议桌

还有其他几个表格可以帮助定义我们的会议计划选项。

会议时间表

此表包含按开始时间（时间戳）列出的所有建议会议时间（和日期）。 Suggested_by 显示谁建议的时间。而status决定是否选择会议时间。

      $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');      

集合地点表

此表显示了为会议建议的地点：

$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');     

会议日志表

此表记录特定会议的所有添加和更改的历史记录。会议安排期间采取的每个操作都会被记录下来，以提供与会议相关的事件的时间历史记录。它将帮助用户查看一段时间内会议的所有更改的记录，并且还可能帮助我们进行开发调试。

$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');

会议记录表

用户在对会议进行更改时可以来回发送简短的注释。该表记录了这些注释。

$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');     

其他相关用户表

有几个表可以扩展用户定义。

好友表

这是一个索引表，列出了每个用户的好友。它还跟踪他们是否是最喜欢的朋友以及他们举行的会议数量。这可能有助于简化调度体验，例如首先显示最喜欢的或经常的朋友。

$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');     

用户位置表

这是用户喜欢见面或过去见过的地点的索引表。我们将在此处跟踪该用户最喜欢的地点和举行的会议数量。 is_special 字段将指示某个地点是用户自己的家、办公室或会议地点。

$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');     

用户联系表

此表提供特定用户的联系信息，例如电话号码、Skype 地址以及与在这些地方联系用户相关的任何注释。

 $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');     

为了简单起见，我现在将跳过会议模板架构。而且我还没有设计与收入相关的功能。这样做的主要原因是，我目前有很多工作要做，以运行核心功能集并完成本教程系列的前几集。然而，这是一个值得教育的时刻。这是一个很好的例子，说明企业家的资源有限，专注于核心功能，但没有“意识到”创收也是一个核心功能。因为我相信我最初可以在没有收入的情况下启动 Meeting Planner，所以这是我目前能够做出的妥协。

运行数据库迁移

既然您对我们的数据库架构和 Active Record 迁移有了更多的背景知识，那么让我们运行其余部分：

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

您应该看到类似这样的内容：

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.

同样，当我们在生产中安装 Meeting Planner 时，我们也将使用迁移来构建初始数据库。无需导出和导入 SQL 文件，这些文件可能会因我们跨环境使用的版本不同而损坏。

注册管理用户

在我们继续之前，您需要将自己注册为管理用户。单击工具栏中的注册链接，只需注册该应用程序即可。

如果成功，当您返回主页时，您将看到工具栏指示您的登录状态.

这些表单和应用逻辑都包含在 Yii 的高级应用模板中。

使用 Yii 的 Gii 构建脚手架

现在我们可以构建脚手架来支持常见的创建、读取、更新和删除操作 (CRUD) 的模型视图控制器代码。

我们将使用 Gii（Yii 令人惊叹的自动代码生成器）来构建许多基本框架代码。这个名字可能很愚蠢，但它非常强大并且是 Yii 开发的核​​心。我们将从会议和地点开始。

使用 Gii

将浏览器指向 http://localhost:8888/mp/gii。你应该看到这个：

生成模型

使用 Gii 构建时，通常从每个表的模型生成器开始。在使用模型生成器之前，您必须先运行迁移以在数据库中创建表，就像我们上面所做的那样。 Gii 使用 SQL 表定义为您的模型生成代码。

让我们使用模型生成器为会议桌生成模型代码。代码已在您的 Github 存储库中生成，但您可以随意再次运行这些练习。 Gii 将为您预览并可选择覆盖代码。

按如下所示为会议模型填写模型生成器：

然后，生成 Place 模型：

Gii 非常令人惊奇——基于我们的表定义，它生成了大量的逻辑。

在 /mp/frontend/models/Meeting.php 模型中，您将看到自动生成的属性标签：

    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',
        ];
    }

它为表单生成字段验证规则：

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

它会生成数据库关系 - 以下是一些示例：

/* @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']);
    }

生成CRUD

现在，我们可以使用 CRUD 生成器来构建用于创建、读取、更新和删除操作的代码。

访问 CRUD 生成器并为会议创建它。请注意，前端是用户将看到的 Yii 应用程序。

当您点击预览时，您应该会看到如下内容：

当您点击生成时，您应该会看到以下结果：

接下来，对地点重复上述过程。

Now you can actually browse the sessions and locations on our website to see the actual generated code. Point your browser to http://localhost:8888/mp/meeting. It should look like this:

If you signed up for an account, you should be able to create meetings. Note that Gii doesn't know the difference between the fields that our code should manage and the fields that the user provides. We'll clean this up in the next tutorial. Currently you need to enter an integer for owner_id (use 1 - this is the first logged in user), meeting_type, status, created_at and updated_at:

After a few meetings have been created, the meeting index page will look like this:

p>

Combining the power of Gii and Yii allows you to build web applications faster than otherwise. Amazingly, we can easily use Bootstrap responsively built working controllers and forms with just a database table structure and a piece of migration code.

What’s next?

I hope you are interested in the database and Gii walkthrough. The next article in this series will focus on building features around places. It'll show you how to use Google Places, Google Maps, and HTML5 geolocation to build the functionality you need for Meeting Planner. If you want to get a head start on these topics, I wrote a tutorial on it: How to use Zillow Neighborhood Maps with HTML5 Geolocation.

Feel free to add your questions and comments below; I'll generally join in on the discussion. You can also contact me on Twitter @reifman or email me directly.

Related Links

• Programming with Yii2: Getting Started
• Yii Framework Introduction

The above is the detailed content of Design a startup’s database using PHP and outline functional requirements. For more information, please follow other related articles on the PHP Chinese website!