Database design | Hairdressing App

This document outlines the database design

Edit me

1. Design

2. Database and user creation

CREATE DATABASE IF NOT EXISTS hair_project_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

CREATE USER IF NOT EXISTS 'dev_admin'@'localhost' IDENTIFIED BY 'administrator';

GRANT ALL PRIVILEGES ON hair_project_db.* TO 'dev_admin'@'localhost' IDENTIFIED BY 'administrator';

FLUSH PRIVILEGES;

3. Tables

3.1 Users table

users
id	bigint
username	varchar(32)
user_password	varchar(512)
user_emal	varchar(512)
first_name	varchar(128)
last_name	varchar(128)
role	ENUM(‘admin’, ‘developer’, ‘user’)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.users
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `user_name`     VARCHAR(32) NOT NULL,
    `user_password` VARCHAR(512) NOT NULL,
    `user_email`    VARCHAR(512) NOT NULL,
    `first_name`    VARCHAR(128) NOT NULL DEFAULT 'user',
    `last_name`     VARCHAR(128),
    `user_role`     ENUM('admin', 'developer', 'user') NOT NULL DEFAULT 'user',
    `date_created` DATETIME NOT NULL DEFAULT NOW(),
    `date_modified` DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX (`id`)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.2 User Features table

user_features
id	bigint
user_id	bigint
face_shape_id	bigint
skin_tone_id	bigint
hair_style_id	bigint
hair_length_id	bigint
hair_colour_id	bigint
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.user_features
(
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `user_id`        BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `face_shape_id`  BIGINT NOT NULL,
    `skin_tone_id`   BIGINT NOT NULL,
    `hair_style_id`  BIGINT NOT NULL,
    `hair_length_id` BIGINT NOT NULL,
    `hair_colour_id` BIGINT NOT NULL,
    `date_created`  DATETIME NOT NULL DEFAULT NOW(),
    `date_modified`  DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX (`id`),
    FOREIGN KEY (`user_id`)
        REFERENCES hair_project_db.users (`id`)
        ON DELETE CASCADE
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.3 Face shapes table

face_shapes
id	bigint
shape_name	varchar(128)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.face_shapes
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `shape_name`    VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created` DATETIME NOT NULL DEFAULT NOW(),
    `date_modified` DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX (`id`)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.4 Face shape links table

face_shape_links
id	bigint
face_shape_id	bigint
link_name	varchar(128)
link_url	varchar(512)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.face_shape_links
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `face_shape_id` BIGINT UNSIGNED NOT NULL,
    `link_name`     VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `link_url`      VARCHAR(512) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created` DATETIME NOT NULL DEFAULT NOW(),
    `date_modified` DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX(`id`),
    FOREIGN KEY (`face_shape_id`)
        REFERENCES hair_project_db.face_shapes (`id`)
        ON DELETE CASCADE
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.5 Hair styles table

hair_styles
id	bigint
hair_style_name	varchar(128)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.hair_styles
(
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `hair_style_name` VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created`   DATETIME NOT NULL DEFAULT NOW(),
    `date_modified`   DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX (`id`)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.6 Hair style links table

hair_style_links
id	bigint
hair_style_id	bigint
link_name	varchar(128)
link_url	varchar(512)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.hair_style_links
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `hair_style_id` BIGINT UNSIGNED NOT NULL,
    `link_name`     VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `link_url`      VARCHAR(512) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created` DATETIME NOT NULL DEFAULT NOW(),
    `date_modified` DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX(`id`),
    FOREIGN KEY (`hair_style_id`)
        REFERENCES hair_project_db.hair_styles (`id`)
        ON DELETE CASCADE
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.7 Hair lengths table

hair_lengths
id	bigint
hair_length_name	varchar(128)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.hair_lengths
(
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `hair_length_name` VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created`    DATETIME NOT NULL DEFAULT NOW(),
    `date_modified`    DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX (`id`)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.8 Hair length links table

hair_length_links
id	bigint
hair_length_id	bigint
link_name	varchar(128)
link_url	varchar(512)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.hair_length_links
(
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `hair_length_id` BIGINT UNSIGNED NOT NULL,
    `link_name`      VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `link_url`       VARCHAR(512) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created`  DATETIME NOT NULL DEFAULT NOW(),
    `date_modified`  DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX(`id`),
    FOREIGN KEY (`hair_length_id`)
        REFERENCES hair_project_db.hair_lengths (`id`)
        ON DELETE CASCADE
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.9 Skin tones table

skin_tones
id	bigint
skin_tone_name	varchar(128)
skin_tone_colour_id	bigint
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.skin_tones
(
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `skin_tone_name` VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created`  DATETIME NOT NULL DEFAULT NOW(),
    `date_modified`  DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX (`id`)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.10 Skin tone links table

skin_tone_links
id	bigint
skin_tone_id	bigint
link_name	varchar(128)
link_url	varchar(512)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.skin_tone_links
(
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `skin_tone_id`  BIGINT UNSIGNED NOT NULL,
    `link_name`     VARCHAR(128) NOT NULL DEFAULT '** ERROR: missing category **',
    `link_url`      VARCHAR(512) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created` DATETIME NOT NULL DEFAULT NOW(),
    `date_modified` DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX(`id`),
    FOREIGN KEY (`skin_tone_id`)
        REFERENCES hair_project_db.skin_tones (`id`)
        ON DELETE CASCADE
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

3.11 Colours table

colours
id	bigint
colour_name	varchar(64)
colour_haSH	varchar(64)
date_created	datetime
date_updated	datetime

CREATE TABLE IF NOT EXISTS hair_project_db.colours
(
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY UNIQUE,
    `colour_name`   VARCHAR(64) NOT NULL DEFAULT '** ERROR: missing category **',
    `colour_hash`   VARCHAR(64) NOT NULL DEFAULT '** ERROR: missing category **',
    `date_created` DATETIME NOT NULL DEFAULT NOW(),
    `date_modified` DATETIME DEFAULT NULL ON UPDATE NOW(),
    INDEX(`id`)
)
CHARACTER SET utf8mb4
COLLATE utf8mb4_general_ci
ENGINE = INNODB;

Tags: