Database Definition

We’ll create three .sql files in the data directory: database.sql, structure.sql, and content.sql.

Let’s get started.

Database schema

The main purpose of the intended application is to track the time we spend on a given project. Each project consists of a number of tasks. All the projects are stored in a relational database called tracker. The relational database management system that is used in this context is MySQL.

In the root directory of our project, we’ll create a new folder called data/:

mkdir data & cd data

Then, we’ll create the following .sql files in our new folder:

database.sql

touch database.sql

This file will be in charge of creating our database and selecting it for use. We’ll add the following SQL code within it:

CREATE DATABASE IF NOT EXISTS tracker CHARACTER SET UTF8 COLLATE UTF8_UNICODE_CI;
USE tracker; 

The name of the database we’ll create is tracker.

structure.sql

touch structure.sql

We can now create tables for our database:

DROP TABLE IF EXISTS tasks, projects;

CREATE TABLE projects (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    category VARCHAR(100) NOT NULL
);

CREATE TABLE tasks (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    date_task DATE NOT NULL,
    time_task INT(3) NOT NULL,
    project_id INT(11) NOT NULL,
    CONSTRAINT fk_tas_pro 
        FOREIGN KEY (project_id) 
        REFERENCES projects(id)
        ON DELETE CASCADE
)

Note: The script shown above is in charge of creating two tables —the tasks and the projects table— if they don’t already exist in the database. Each table has properties, like its id and title. The tasks table depends on the projects table and we’ll use the foreign key and the references constraint to link them together.

content.sql

touch content.sql

We can now populate our tables with some initial content:

INSERT INTO projects
VALUES
    (1, 'Learn Laravel', 'Personal'),
    (2, 'Learn Reactjs', 'Personal'),
    (3, 'Project 3', 'Business');

INSERT INTO tasks
VALUES
    (1, 'Task 1', '2020-08-01', 25, 1),
    (2, 'Task 2', '2020-09-15', 45, 2),
    (3, 'Task 3', '2020-09-09', 25, 3);  

Our file structure will look like this: