A DBMS Side Trip

A little break from the direction of my last post; in finishing up the Custom Finder Methods tutorial I realized that I really wanted to work with a database that was configured for CakePHP's naming conventions.  Plus I decided to make a couple of other modifications, such as dropping the dept_emp linking table (instead, employees are owned by departments) and adding a users table for subsequent authentication.

Here's the modified database, from which I'll be doing several future tutorials.

An Employee Database

DROP DATABASE IF EXISTS employees;
CREATE DATABASE IF NOT EXISTS employees;
USE employees;

SELECT 'CREATING DATABASE STRUCTURE' as 'INFO';

DROP TABLE IF EXISTS dept_emp_links,
                     dept_managers,
                     titles,
                     salaries, 
                     employees, 
                     departments;

   set storage_engine = InnoDB;

select CONCAT('storage engine: ', @@storage_engine) as INFO;

CREATE TABLE users (
    id          INT             NOT NULL AUTO_INCREMENT,
    username    VARCHAR(50)     NOT NULL,
    password    VARCHAR(255)    NOT NULL,
    role        ENUM ('administrator','editor', 'contibutor', 'viewer')  NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE departments (
    id          INT             NOT NULL AUTO_INCREMENT,
    code        CHAR(4)         NOT NULL,
    name        VARCHAR(40)     NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE  KEY (code)
);

CREATE TABLE employees (
    id          INT             NOT NULL AUTO_INCREMENT,
    birth_date  DATE            NOT NULL,
    first_name  VARCHAR(20)     NOT NULL,
    last_name   VARCHAR(30)     NOT NULL,
    gender      ENUM ('M','F')  NOT NULL,    
    hire_date   DATE            NOT NULL,
    department_id   INT         NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (department_id),
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE,
    PRIMARY KEY (id)
);

CREATE TABLE dept_managers (
    id           INT             NOT NULL AUTO_INCREMENT,
    department_id    INT         NOT NULL,
    employee_id      INT         NOT NULL,
    from_date    DATE            NOT NULL,
    to_date      DATE            NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (employee_id),
    KEY         (department_id),
    FOREIGN KEY (employee_id)  REFERENCES employees (id)    ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments (id) ON DELETE CASCADE,
    PRIMARY KEY (id)
); 


CREATE TABLE positions (
    id          INT             NOT NULL AUTO_INCREMENT,
    employee_id INT             NOT NULL,
    title       VARCHAR(50)     NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (employee_id),
    FOREIGN KEY (employee_id)  REFERENCES employees (id)    ON DELETE CASCADE,
    PRIMARY KEY (id)
); 
        
CREATE TABLE salaries (
    id          INT             NOT NULL AUTO_INCREMENT,
    employee_id INT             NOT NULL,
    salary      INT             NOT NULL,
    from_date   DATE            NOT NULL,
    to_date     DATE            NOT NULL,
    created     DATETIME DEFAULT NULL,
    modified    DATETIME DEFAULT NULL,
    KEY         (employee_id),
    FOREIGN KEY (employee_id)  REFERENCES employees (id)    ON DELETE CASCADE,
    PRIMARY KEY (id)
);

For convenience, you can copy the above into a file and run the whole thing from the command line.  For example, if you store the script as "employees.sql" you could use:

mysql -u root -p < employees.sql

An App for the Tutorials

Create a CakePHP skeleton app:

composer create-project --prefer-dist cakephp/app cakehrms

Then edit your config/app.php file with the correct parameters needed for your new database:

    'Datasources' => [
        'default' => [
            'className' => 'Cake\Database\Connection',
            'driver' => 'Cake\Database\Driver\Mysql',
            'persistent' => false,
            'host' => 'localhost',
            //'port' => 'nonstandard_port_number',
            'username' => 'root',
            'password' => 'yourpasswordhere',
            'database' => 'employees',
            'encoding' => 'utf8',
            'timezone' => 'UTC',
            'cacheMetadata' => true,
            'log' => false,

Next, bake:

bin/cake bake all --everything

Now you have a CRUD app on the new database from which you can work through several upcoming tutorials from this site.