Webshop/database/db_scripts/webshop_structure.sql

DROP DATABASE IF EXISTS webshop;
CREATE DATABASE IF NOT EXISTS `webshop`
    CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `webshop`;

CREATE TABLE `user`
(
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name`             VARCHAR(255) NOT NULL,
    `lower_name`       VARCHAR(255) NOT NULL,
    `email`            VARCHAR(255) NOT NULL DEFAULT 'NONE',
    `passwd`           VARCHAR(255) NOT NULL DEFAULT 'NONE',
    `passwd_hash_algo` VARCHAR(255) NOT NULL DEFAULT 'NONE',
    `is_admin`         BOOLEAN      NOT NULL DEFAULT '0'
);
ALTER TABLE
    `user`
    ADD UNIQUE `user_email_unique` (`email`);
CREATE TABLE `user_address`
(
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id`       INT UNSIGNED NOT NULL,
    `address_line1` VARCHAR(255) NOT NULL,
    `address_line2` VARCHAR(255) NOT NULL,
    `city`          VARCHAR(255) NOT NULL,
    `postal_code`   INT          NOT NULL,
    `country`       VARCHAR(2)   NOT NULL,
    `telephone`     VARCHAR(255) NOT NULL
);
ALTER TABLE
    `user_address`
    ADD UNIQUE `user_address_telephone_unique` (`telephone`);
CREATE TABLE `user_payment`
(
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id`      INT UNSIGNED NOT NULL,
    `payment_type` VARCHAR(255) NOT NULL,
    `provider`     VARCHAR(255) NOT NULL,
    `account_no`   VARCHAR(255) NOT NULL,
    `expiry`       DATE         NOT NULL
);
CREATE TABLE `shopping_session`
(
    `id`      INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED  NOT NULL,
    `total`   DECIMAL(8, 2) NOT NULL
);
CREATE TABLE `cart_item`
(
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `session_id` INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity`   INT          NOT NULL
);
CREATE TABLE `payment_details`
(
    `id`       INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `order_id` INT          NOT NULL,
    `amount`   INT          NOT NULL,
    `provider` VARCHAR(255) NOT NULL,
    `status`   VARCHAR(255) NOT NULL
);
CREATE TABLE `product`
(
    `id`          INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name`        VARCHAR(255)  NOT NULL,
    `make`        VARCHAR(255)  NOT NULL,
    `model`       VARCHAR(255)  NOT NULL,
    `description` TEXT          NOT NULL,
    `category_id` INT UNSIGNED  NOT NULL,
    `price`       DECIMAL(8, 2) NOT NULL,
    `discount_id` INT UNSIGNED  NOT NULL,
    `created_at`  DATE          NOT NULL
);
CREATE TABLE `discount`
(
    `id`               INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name`             VARCHAR(255)  NOT NULL,
    `description`      TEXT          NOT NULL,
    `discount_percent` DECIMAL(8, 2) NOT NULL,
    `active`           BOOLEAN       NOT NULL
);
CREATE TABLE `order_items`
(
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `order_id`   INT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED NOT NULL,
    `quantity`   INT          NOT NULL,
    `user_id`    BIGINT       NOT NULL
);
CREATE TABLE `product_category`
(
    `id`   INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL
);
CREATE TABLE `order_details`
(
    `id`         INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id`    INT UNSIGNED  NOT NULL,
    `total`      DECIMAL(8, 2) NOT NULL,
    `payment_id` INT UNSIGNED  NOT NULL
);
CREATE TABLE `product_pictures`
(
    `id`             INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `product_id`     INT UNSIGNED NOT NULL,
    `picture_base64` TEXT         NOT NULL,
    `is_primary`     BOOLEAN      NOT NULL
);
ALTER TABLE
    `product_pictures`
    ADD CONSTRAINT `product_pictures_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);
ALTER TABLE
    `user_payment`
    ADD CONSTRAINT `user_payment_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
ALTER TABLE
    `order_details`
    ADD CONSTRAINT `order_details_payment_id_foreign` FOREIGN KEY (`payment_id`) REFERENCES `payment_details` (`id`);
ALTER TABLE
    `order_details`
    ADD CONSTRAINT `order_details_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
ALTER TABLE
    `order_items`
    ADD CONSTRAINT `order_items_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);
ALTER TABLE
    `product`
    ADD CONSTRAINT `product_discount_id_foreign` FOREIGN KEY (`discount_id`) REFERENCES `discount` (`id`);
ALTER TABLE
    `shopping_session`
    ADD CONSTRAINT `shopping_session_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
ALTER TABLE
    `product`
    ADD CONSTRAINT `product_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `product_category` (`id`);
ALTER TABLE
    `user_address`
    ADD CONSTRAINT `user_address_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
ALTER TABLE
    `order_items`
    ADD CONSTRAINT `order_items_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `order_details` (`id`);
ALTER TABLE
    `cart_item`
    ADD CONSTRAINT `cart_item_session_id_foreign` FOREIGN KEY (`session_id`) REFERENCES `shopping_session` (`id`);
ALTER TABLE
    `cart_item`
    ADD CONSTRAINT `cart_item_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);