Definition

Diagram

MySQL

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`)
);

Brand

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
Key	string	key	varchar(32)	true	unique key
BrandName	string	brand_name	varchar(127)	true
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Category

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
Ref	uint64	ref	bigint(20) unsigned	true	ref to id
Key	string	key	varchar(32)	true	unique key
CategoryName	string	category_name	varchar(127)	true
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Product

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
CategoryID	uint64	category_id	bigint(20) unsigned	true
BrandID	uint64	brand_id	bigint(20) unsigned	true
Key	string	key	varchar(32)	true	unique key
Title	string	title	varchar(127)	true
SubTitle	*string	sub_title	varchar(255)	false
Slug	*string	slug	varchar(255)	false	friendly format url
Price	float64	price	decimal(10,2)	true	current price
Discount	float64	discount	decimal(10,2)	true	discount
Content	*string	content	text	false
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Tag

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
Ref	uint64	ref	bigint(20) unsigned	true	ref to id
Key	string	key	varchar(32)	true	unique key
TagName	string	tag_name	varchar(127)	true
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Version

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
ProductID	uint64	product_id	bigint(20) unsigned	true
VersionName	string	version_name	varchar(127)	true
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Color

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
ProductID	uint64	product_id	bigint(20) unsigned	true
ColorName	string	color_name	varchar(127)	true
ColorCode	string	color_code	varchar(127)	true	color code like #FFFFFF
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

Image

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
ProductID	uint64	product_id	bigint(20) unsigned	true
ImageSrc	*string	image_src	varchar(255)	false
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false

ProductTag

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ProductID	uint64	product_id	bigint(20) unsigned	true
TagID	uint64	tag_id	bigint(20) unsigned	true

Sku

AttributeName	AttributeType	ColumnName	DataType	NotNull	Comment
ID	uint64	id	bigint(20) unsigned	true
ProductID	uint64	product_id	bigint(20) unsigned	true
VersionID	uint64	version_id	bigint(20) unsigned	true
ColorID	uint64	color_id	bigint(20) unsigned	true
SkuName	string	sku_name	varchar(127)	true
Qty	int	qty	int(10)	true	quantity
Price	float64	price	decimal(10,2)	true	current price
Discount	float64	discount	decimal(10,2)	true	discount
Seq	int8	seq	tinyint(3)	true	sequence
Status	int	status	int(10)	true	-1 deleted 0 pendding 1 valid
CreatedAt	*time.Time	created_at	timestamp	false
UpdatedAt	*time.Time	updated_at	timestamp	false