Categories
Beginner Tutorial

Surveys CakePHP Tutorial Part 1

Last Thursday I received a request to do an application tutorial, so I figured no better time than Tutorial Tuesday! In this two-part tutorial, we will be covering some of the basics of CakePHP application development starting today with database design. I will walk you through different association types and how you can use them in your app. In part two, coming Thursday, we’ll be doing a deep dive into the codebase. Let’s make a database already.

Creating the Database

When creating a CRUD application, Create, Read, Update, and Delete, your database design can make your life much easier, or make your code full of workarounds and hacks. Since we are creating a surveys app we will be concerned with three things,

  1. Surveys
  2. Users
  3. User Permissions

Modeling Surveys

Surveys will be the most complex to model because we are taking into account that the survey creator may want a question to be answered via multiple-choice or short answer.

We will need the base survey table.

CREATE TABLE surveys (
    id INT AUTO_INCREMENT PRIMARY KEY,
  	title VARCHAR(255) NOT NULL
);

Pretty simple survey so far. Let’s add some questions. Since surveys can have many questions I’ll show you how to model a hasMany relationship in your database.

CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    question_type ENUM ('multiple-choice', 'short-answer'),
    question TEXT NOT NULL
);

We have a few interesting things happening here. The ENUM data type allows us to only accept answers from a set. Since we only want the questions to be either multiple choice or short answer we’ve limited the potential selections to just those.

We also have the survey_id field. This field tells CakePHP that survey hasMany questions! Now when we are creating our application we can have as many questions as we want!

What about answers? Since a question can be multiple choice our questions will have a hasMany relationship with our answers as well.

CREATE TABLE answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    answer TEXT
);

Now we have a survey that can have multiple questions which can then have multiple answers!

What good are surveys if there’s no one to fill them out?

Adding Users

Users are straightforward to model. We’ll assume they will sign up with an email and password.

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,

    created DATETIME,
    modified DATETIME 
);

What if a user wants to create their own survey? We’ll need to connect the two tables together somehow!

Connecting Users and Surveys

Since we are obviously making this app with Enterprise use in mind we’ll have to take into account that a survey can be owned by multiple users, but also that a user can own multiple surveys. That means a survey can have many users and a user can have many surveys!

This type of relationship is called ManyToMany or BelongsToMany, and luckily it’s easy to model. We’ll just need to add one additional table defining the relationship between surveys and users.

CREATE TABLE surveys_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    user_id INT NOT NULL
);

The surveys_users table allows us to have a many to many relationships by keeping track of which survey and user are related.

In this application, we’ll also be assigning different roles to users. That means some users will only be allowed to answer surveys, some will be able to create and edit their surveys, and a few will have complete control over all surveys.

User Permissions

In order to store user permissions in the database, we’ll need to add another table. We could put the role in the users table, but to keep things modular we’ll give roles their own table.

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    role ENUM ('user', 'admin', 'root')
);

We’ve pulled this trick before. A user hasOne role. The difference between the hasOne and hasMany relationships are defined in our application which we will work on in part 2.

All Together Now

Here is the full database code if you didn’t want to copy and paste all of the code blocks above individually.

use my_database;
-- ^ Put your database name here

CREATE TABLE surveys (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL
);

CREATE TABLE questions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    question_type ENUM ('multiple-choice', 'short-answer'),
    question TEXT NOT NULL
);

CREATE TABLE answers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    question_id INT NOT NULL,
    answer TEXT
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,

    created DATETIME,
    modified DATETIME 
);

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    role ENUM ('user', 'admin', 'root')
);

CREATE TABLE surveys_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    survey_id INT NOT NULL,
    user_id INT NOT NULL
);

I hope that you liked the walkthrough of the database design above. In the next part, we’ll be completing the application! Subscribe to get notified of when part 2 comes out. Comment to tell me neat ideas you want to implement in your billion-dollar surveys app, so I can steal them!

See ya Thursday.