CREATE TABLE IF NOT EXISTS `brands` (
`id` bigint(20) unsigned NOT NULL,
`key` varchar(32) NOT NULL COMMENT 'unique key',
`brand_name` varchar(127) NOT NULL,
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `categories` (
`id` bigint(20) unsigned NOT NULL,
`ref` bigint(20) unsigned NOT NULL COMMENT 'ref to id',
`key` varchar(32) NOT NULL COMMENT 'unique key',
`category_name` varchar(127) NOT NULL,
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `products` (
`id` bigint(20) unsigned NOT NULL,
`category_id` bigint(20) unsigned NOT NULL,
`brand_id` bigint(20) unsigned NOT NULL,
`key` varchar(32) NOT NULL COMMENT 'unique key',
`title` varchar(127) NOT NULL,
`sub_title` varchar(255),
`slug` varchar(255) COMMENT 'friendly format url',
`price` decimal(10,2) NOT NULL COMMENT 'current price',
`discount` decimal(10,2) NOT NULL COMMENT 'discount',
`content` text,
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_products_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`),
CONSTRAINT `fk_products_brand_id` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`)
);
CREATE TABLE IF NOT EXISTS `tags` (
`id` bigint(20) unsigned NOT NULL,
`ref` bigint(20) unsigned NOT NULL COMMENT 'ref to id',
`key` varchar(32) NOT NULL COMMENT 'unique key',
`tag_name` varchar(127) NOT NULL,
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE IF NOT EXISTS `versions` (
`id` bigint(20) unsigned NOT NULL,
`product_id` bigint(20) unsigned NOT NULL,
`version_name` varchar(127) NOT NULL,
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_versions_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
);
CREATE TABLE IF NOT EXISTS `colors` (
`id` bigint(20) unsigned NOT NULL,
`product_id` bigint(20) unsigned NOT NULL,
`color_name` varchar(127) NOT NULL,
`color_code` varchar(127) NOT NULL COMMENT 'color code like #FFFFFF',
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_colors_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
);
CREATE TABLE IF NOT EXISTS `images` (
`id` bigint(20) unsigned NOT NULL,
`product_id` bigint(20) unsigned NOT NULL,
`image_src` varchar(255),
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_images_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`)
);
CREATE TABLE IF NOT EXISTS `product_tags` (
`product_id` bigint(20) unsigned NOT NULL,
`tag_id` bigint(20) unsigned NOT NULL,
PRIMARY KEY (`product_id`, `tag_id`),
CONSTRAINT `fk_product_tags_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
CONSTRAINT `fk_product_tags_tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)
);
CREATE TABLE IF NOT EXISTS `skus` (
`id` bigint(20) unsigned NOT NULL,
`product_id` bigint(20) unsigned NOT NULL,
`version_id` bigint(20) unsigned NOT NULL,
`color_id` bigint(20) unsigned NOT NULL,
`sku_name` varchar(127) NOT NULL,
`qty` int(10) NOT NULL COMMENT 'quantity',
`price` decimal(10,2) NOT NULL COMMENT 'current price',
`discount` decimal(10,2) NOT NULL COMMENT 'discount',
`seq` tinyint(3) NOT NULL COMMENT 'sequence',
`status` int(10) NOT NULL COMMENT '-1 deleted 0 pendding 1 valid',
`created_at` timestamp NULL,
`updated_at` timestamp NULL,
PRIMARY KEY (`id`),
CONSTRAINT `fk_skus_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
CONSTRAINT `fk_skus_version_id` FOREIGN KEY (`version_id`) REFERENCES `versions` (`id`),
CONSTRAINT `fk_skus_color_id` FOREIGN KEY (`color_id`) REFERENCES `colors` (`id`)
);