The schema

Our app is going to be an employee records maintenance system of the Lorem Ipsum Company, where user admins can view, add, edit and delete employee records. These should cover the basic operations done on database tables and could easily be adapted and extended for use on products or services instead of personnel. Later, we will extend the project into a timekeeping system similar to a time-in / time-out bundy clock system where employees clock in and clock out.

We will name the database empdb. For now, the two tables we will create inside empdb will be the admins and employees tables.

The employees table – containing the employee records:

id - the row id, the primary key incremented automatically, integer

empid - the employee id number, string

deprt - department name, string

paygd - salary grade, string

email - email address, will also be used as username, string

pword - password, string

fname - firtstname, string

lname - lastname, string

phone - phone number, string

photo - full path to the employee photo file, string

street - street address minus the city, string

city - the city part of the address, string

province - the province part of the address, string

postcode - postal code of the address, string

The admins table - the administrators of the employee records:

id - the row id, the primary key incremented automatically, integer

email - email address, which will serve as username, string

password - password, string

So here is the SQL we are going to write in the MySQL Workbench SQL script window:

drop database if exists empdb;
create database empdb;
use empdb;
drop table if exists employees;
create table employees
(
        id int auto_increment primary key,
        empid char(4) not null unique,
        deprt varchar(30) not null,
        paygd varchar(10) default 'none yet',
        email varchar(40) not null unique,
        pword varchar(255) not null,
        fname varchar(30) not null,
        lname varchar(30) not null,
        phone varchar(20) default '[phone]',
        photo varchar(50) default 'No photo submitted',
        street varchar(50) default '123 First St.',
        city varchar(30) default 'Toronto',
        province char(2) default 'ON',
        postcode varchar(10) default 'A1A 1A1',
        created_at timestamp default current_timestamp,
        updated_at timestamp on update current_timestamp
);
drop table if exists admins;
create table admins
(
        id int auto_increment primary key,
        email varchar(40) not null unique,
        pword varchar(255) not null
);

After typing in these SQL statements on the MySQL Workbench SQL script window, click on the lightning icon to execute.

After that, our MySQL database back-end should be ready.