One of the most important steps in starting a new project is planning it. By planning the project before we begin programming, we can easily identify most (if not all) models that our application will use, key features that we’ll need to implement, as well as any areas that may cause us problems while developing our applications. Breaking down the project beforehand also helps us estimate how long it will take to develop each part of our applications as well as the application as a whole. While requirements and expectations for our application will most likely change during its development, identifying the core components of your application will help ensure that the core functionality of our application works as we intend.
For our task management application, there are two main components: tasks and projects. Let’s break each of these components down.
The first component of our application is tasks. A task is an item that needs to be done by our user and usually consists of a brief, concise title, and a description of what needs to be done to complete that task. Sometimes, a task has a due date or time associated with it that lets us know when the task needs to be completed. Tasks also need to indicate whether they have been completed or not. Finally, a task is usually associated with a group or project that contains similar or related tasks.
The second component of our application is projects. Projects group related tasks together and usually have a descriptive name associated with them. Projects may also have a due date or time associated with them, which indicates when all tasks in a project need to be completed. We also need to be able to indicate whether or not a project is completed.
By breaking down our project, we’ve also identified a third component of our application: users. Users in our application will have the ability to create and manage both projects and tasks as well as view the statuses and due dates of any given task. While this component of our application may seem obvious, identifying it early on allows us to better understand the interaction that our users will have with the various components of our application
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
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 (
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.