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:

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:

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

Meeting Planner Yii2 Home Page

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

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:

You can run the first migration as shown below:

You should see something like this:

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:

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:

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.

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

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:

Meeting Planner Migrations

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:

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:

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.

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.

The Meeting Place Table

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

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.

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.

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.

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.

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.

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:

You should see something like this:

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.

Meeting Planner Signup

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

Meeting Planner Toolbar Logged In

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:

Meeting Planner Yii2 Gii CRUD Menu

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:

It generates field validation rules for forms:

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

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.

Meeting Planner Yii2 Gii CRUD Generator

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:

Meeting Planner Meeting Controller

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:

Meeting Planner Meeting Create Form

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.

Related Links



Related Articles