shop.sql

CREATE TABLE `products` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`sku` varchar NOT NULL,
	`title` varchar NOT NULL,
	`description` TEXT NOT NULL,
	`description_mini` TEXT NOT NULL,
	`price` FLOAT NOT NULL,
	`date` DATE NOT NULL,
	`category_ids(JSON)` INT NOT NULL,
	`payments_types(json)` INT NOT NULL,
	PRIMARY KEY (`id`,`sku`)
);

CREATE TABLE `categories` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` varchar NOT NULL,
	`parent_id` INT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `product_photos` (
	`id` INT NOT NULL,
	`src` TEXT NOT NULL,
	`date` DATE NOT NULL,
	`product_id` INT NOT NULL
);

CREATE TABLE `orders` (
	`id` INT NOT NULL,
	`user_id` INT NOT NULL,
	`date` DATETIME NOT NULL,
	`price` FLOAT NOT NULL,
	`products(json)` varchar NOT NULL,
	`payment_type_id` INT NOT NULL,
	`payment status` BOOLEAN NOT NULL,
	`shipping_type_id` INT NOT NULL
);

CREATE TABLE `payments_methods` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` varchar NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (`id`)
);

CREATE TABLE `site_setting` (
	`site_name` varchar NOT NULL,
	`currency` varchar NOT NULL
);

CREATE TABLE `users` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) NOT NULL,
	`email` VARCHAR(255) NOT NULL,
	`password` varchar(256) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `customers` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`type` INT NOT NULL,
	`email` varchar NOT NULL,
	`first_name` varchar NOT NULL,
	`last_name` varchar NOT NULL,
	`street` varchar NOT NULL,
	`city` varchar NOT NULL,
	`zip` varchar NOT NULL,
	`country` varchar NOT NULL,
	`date_of_bithday` DATE NOT NULL,
	`denger` TEXT NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `shipping_methods` (
	`id` BINARY NOT NULL AUTO_INCREMENT,
	`name` varchar NOT NULL,
	`price` FLOAT NOT NULL,
	PRIMARY KEY (`id`)
);

ALTER TABLE `products` ADD CONSTRAINT `products_fk0` FOREIGN KEY (`category_ids(JSON)`) REFERENCES `categories`(`id`);

ALTER TABLE `products` ADD CONSTRAINT `products_fk1` FOREIGN KEY (`payments_types(json)`) REFERENCES `payments_methods`(`id`);

ALTER TABLE `categories` ADD CONSTRAINT `categories_fk0` FOREIGN KEY (`parent_id`) REFERENCES `categories`(`id`);

ALTER TABLE `product_photos` ADD CONSTRAINT `product_photos_fk0` FOREIGN KEY (`product_id`) REFERENCES `products`(`id`);

ALTER TABLE `orders` ADD CONSTRAINT `orders_fk0` FOREIGN KEY (`user_id`) REFERENCES `customers`(`id`);

ALTER TABLE `orders` ADD CONSTRAINT `orders_fk1` FOREIGN KEY (`payment_type_id`) REFERENCES `payments_methods`(`id`);

ALTER TABLE `orders` ADD CONSTRAINT `orders_fk2` FOREIGN KEY (`shipping_type_id`) REFERENCES `shipping_methods`(`id`);