A database schema for an e-commerce storefront-inventory-backoffice that specializes in selling apparels and ready-made clothing as well as smartphones using either MySQL, SQLite or PostgreSQL as primary database

ecommerce_storefront_design_databse.sql

-- SKU for Clothes: [Shirt Style]-[Color]-[Size]-[Material]-[Brand]-[Collection Month]-[Collection Year] (e.g. Material=spandex,cotton;Size=medium,small,large,extra-large;)
-- SKU for Smartphones: [Network]-[Color]-[Weight]-[OS]-[SIM Type]-[SIM Count]-[Display]-[Brand]-[Model] (e.g. Network=gsm;SIM Type=nano,micro;)

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci;  -- utf8mb4_unicode_ci


CREATE TABLE products ();

INSERT INTO products (
	name ,
	brand_id ,
	category_id ,
	attributes
)
VALUES(
	'Desire' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "CDMA" , "HSPA" , "EVDO") ,
		"body" ,
		"5.11 x 2.59 x 0.46 inches" ,
		"weight" ,
		"143 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"4.5 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android Jellybean v4.3"
	)
);

INSERT INTO products (
	name ,
	brand_id ,
	category_id ,
	attributes
)
VALUES(
	'Passion' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "CDMA" , "HSPA") ,
		"body" ,
		"6.11 x 3.59 x 0.46 inches" ,
		"weight" ,
		"145 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"4.5 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android Jellybean v4.3"
	)
);

INSERT INTO products (
	name ,
	brand_id ,
	category_id ,
	attributes
)
VALUES(
	'Emotion' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "CDMA" , "EVDO") ,
		"body" ,
		"5.50 x 2.50 x 0.50 inches" ,
		"weight" ,
		"125 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"5.00 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android KitKat v4.3"
	)
);

INSERT INTO products (
	name ,
	brand_id ,
	category_id ,
	attributes
)
VALUES(
	'Sensation' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("GSM" , "HSPA" , "EVDO") ,
		"body" ,
		"4.00 x 2.00 x 0.75 inches" ,
		"weight" ,
		"150 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"3.5 inches" ,
		"resolution" ,
		"720 x 1280 pixels" ,
		"os" ,
		"Android Lollipop v4.3"
	)
);

INSERT INTO products (
	name ,
	brand_id ,
	category_id ,
	attributes
)
VALUES(
	'Joy' ,
	'2' ,
	'2' ,
	JSON_OBJECT(
		"network" ,
		JSON_ARRAY("CDMA" , "HSPA" , "EVDO") ,
		"body" ,
		"7.00 x 3.50 x 0.25 inches" ,
		"weight" ,
		"250 grams" ,
		"sim" ,
		"Micro-SIM" ,
		"display" ,
		"6.5 inches" ,
		"resolution" ,
		"1920 x 1080 pixels" ,
		"os" ,
		"Android Marshmallow v4.3"
	)
);