The database

With the core components of our application identified, we can now begin to think about what our database is going to look like. Let's start with the two database tables.

The tasks table

By looking at our requirements, we can identify several columns and data types for our tasks table. As a rule, each task that we create will have a unique, incrementing ID associated with it. Other columns that we can quickly identify are the task name, the task description, the due date, and whether or not the task has been completed. We also know that each task is going to be associated with a project, which means we need to reference that project in our table.

There are also some columns we can identify that are not so obvious. The two most useful columns that aren't explicitly identified are timestamps for the creation date of the task and the date it was last updated on. By adding these two columns, we can gain useful insights into the use of our application. It's possible that in the future, our imaginary client may want to know how long an unresolved task has been open for and whether or not it needs additional attention if it has not been updated in several days.

With all the columns and data types for our table identified, our tasks table written with generic SQL data types will look as follows:

ID INTEGER PRIMARY KEY
name TEXT
description TEXT
completed BOOLEAN
project_id INTEGER
due_date TIMESTAMP
created TIMESTAMP
updated TIMESTAMP

The projects table

By looking at our requirements for projects, we can easily pick out the major columns for our projects table: a descriptive name, whether or not the project has been completed, and when the project is due. We also know from our tasks table that each project will need to have its own unique ID for the task to reference. When the time comes to create our models in our application, we'll clearly define the one-to-many relationship between any given project and the many tasks belonging to it. If we keep a created and updated column, our projects table written in generic SQL will look as follows:

ID INTEGER PRIMARY KEY
name TEXT
completed BOOLEAN
due_date TIMESTAMP
created TIMESTAMP
updated TIMESTAMP

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Users

Our application requirements also show us that we need to store users somewhere. For this application, we're going to store our users in a flat file database. In Chapter 3, Scheduled Reminders, we will expand upon this and store users in their own database table.

Choosing a database technology

Now that we have decided what our database is going to look like, it's time to start thinking about where we're going to store this information. To help familiarize yourself with the different database adapters Yii natively supports, for this project, we will be using SQLite. Since we now know where we're going to store our data, we can identify all the correct data types for database tables.

The tasks table

Since SQLite only supports five basic data types (NULL, INTEGER, REAL, TEXT, and BLOB), we need to convert a few of the data types we initially identified for this table into ones that SQLite supports. Since SQLite does not support Boolean or timestamps natively, we need to find another way of representing this data using a data type that SQLite supports. We can represent a Boolean value as an integer either as 0 (false) or 1 (true). We can also represent all of our timestamp columns as integers by converting the current date to a Unix timestamp.

With our final data types figured out, our tasks table now will look like this:

ID INTEGER PRIMARY KEY
name TEXT
description TEXT
completed INTEGER
project_id INTEGER
due_date INTEGER
created INTEGER
updated INTEGER

The projects table

By applying the same logic to our projects table, we can derive the following structure for this table:

ID INTEGER PRIMARY KEY
name TEXT
completed INTEGER
due_date INTEGER
created INTEGER
updated INTEGER

The database overview

By spending a few minutes thinking about our application beforehand, we've successfully identified all the tables for our application, how they interact with one another, and all the column names and data types that our application will be using. We've done a lot of work on our application already without even writing a single line of code. By doing this work upfront, we have also reduced some of the work we'll need to do later on when creating our models.