· 7 years ago · Sep 05, 2018, 12:06 PM
1CREATE TABLE IF NOT EXISTS `products` (
2 `productID` INT(11) NOT NULL AUTO_INCREMENT,
3 `name` VARCHAR(100) NOT NULL,
4 `longDescription` TEXT,
5 `shortDescription` VARCHAR(1000),
6 PRIMARY KEY (`productID`)
7);
8INSERT INTO `products` (`productID`, `name`, `longDescription`, `shortDescription`) VALUES
9(1, 'A shirt', 'Long description of this product', 'shortDesc of shirt');
10
11CREATE TABLE IF NOT EXISTS `productpricing` (
12 `productID` INT(11) NOT NULL,
13 `startDate` TIMESTAMP NOT NULL DEFAULT '1971-01-01 00:00:00',
14 `endDate` TIMESTAMP NOT NULL DEFAULT '2099-01-01 00:00:00',
15 `price` DECIMAL(10, 2) NOT NULL,
16 PRIMARY KEY(`productID`, `endDate`),
17 FOREIGN KEY (`productID`) REFERENCES products(`productID`)
18);
19INSERT INTO `productpricing` (`productID`, `startDate`, `endDate`, `price`) VALUES
20(1, '1971-01-01 00:00:00', '2099-01-01 00:00:00', 309.99);
21
22CREATE TABLE IF NOT EXISTS `categories` (
23 `categoryID` INT(11) NOT NULL AUTO_INCREMENT,
24 `categoryName` VARCHAR(100) NOT NULL,
25 PRIMARY KEY (`categoryID`)
26);
27INSERT INTO `categories` (`categoryID`, `categoryName`) VALUES
28(1, 'Test Category');
29
30CREATE TABLE IF NOT EXISTS `sizes` (
31 `sizeID` INT(11) NOT NULL,
32 `size` INT NOT NULL,
33 PRIMARY KEY (`sizeID`)
34);
35INSERT INTO `sizes` (`sizeID`, `size`) VALUES
36(1, 50),
37(2, 56),
38(3, 62),
39(4, 68),
40(5, 74),
41(6, 80),
42(7, 86);
43
44CREATE TABLE IF NOT EXISTS `colors` (
45 `colorID` INT(11) NOT NULL,
46 `color` VARCHAR(100) NOT NULL,
47 PRIMARY KEY (`colorID`)
48);
49INSERT INTO `colors` (`colorID`, `color`) VALUES
50(1, "Red"),
51(2, "White"),
52(3, "Blue"),
53(4, "Purple");
54
55CREATE TABLE IF NOT EXISTS `product_variants` (
56 `productvariantID` INT(11) NOT NULL,
57 `productID` INT(11) NOT NULL,
58 `categoryID` INT(11) NOT NULL,
59 `colorID` INT(11) NOT NULL,
60 `sizeID` INT(11) NOT NULL,
61 `sku` VARCHAR(50) NOT NULL UNIQUE,
62 `quantity` INT(11) NOT NULL,
63 `isActive` BOOLEAN NOT NULL DEFAULT 0,
64 PRIMARY KEY (`productvariantID`),
65 UNIQUE (`productID`, `colorID`, `sizeID`),
66 FOREIGN KEY (`productID`) REFERENCES products(`productID`),
67 FOREIGN KEY (`categoryID`) REFERENCES categories(`categoryID`),
68 FOREIGN KEY (`colorID`) REFERENCES colors(`colorID`),
69 FOREIGN KEY (`sizeID`) REFERENCES sizes(`sizeID`)
70);
71INSERT INTO `product_variants` (`productvariantID`, `productID`, `categoryID`, `colorID`, `sizeID`, `sku`, `quantity`, `isActive`) VALUES
72(1, 1, 1, 2, 1, 'clalb121', 2, 1),
73(2, 1, 1, 3, 2, 'clalb132', 1, 1),
74(3, 1, 1, 2, 2, 'clalb122', 5, 1);
75
76CREATE TABLE IF NOT EXISTS `images` (
77 `imageID` INT(11) NOT NULL AUTO_INCREMENT,
78 `imageFilename` VARCHAR(100) NOT NULL,
79 PRIMARY KEY(`imageID`)
80);
81INSERT INTO `images` (`imageID`, `imageFilename`) VALUES
82(1, 'shirtwhite1.jpg'),
83(2, 'shirtwhite2.jpg'),
84(3, 'shirtblue1.jpg'),
85(4, 'shirtblue2.jpg');
86
87CREATE TABLE IF NOT EXISTS `product_variant_images` (
88 `productvariantID` INT(11) NOT NULL,
89 `imageID` INT(11) NOT NULL,
90 FOREIGN KEY(`productvariantID`) REFERENCES product_variants(`productvariantID`),
91 FOREIGN KEY(`imageID`) REFERENCES images(`imageID`)
92);
93INSERT INTO `product_variant_images` (`productvariantID`, `imageID`) VALUES
94(1, 1),
95(1, 2),
96(2, 3),
97(2, 4),
98(3, 1),
99(3, 2);
100
101SELECT
102 p.productID,
103 p.name,
104 p.longDescription,
105 p.shortDescription,
106 colors.color,
107 sizes.size,
108 pvar.quantity,
109 pprice.price,
110 images.imageFilename
111FROM products as p
112JOIN product_variants as pvar
113 ON p.productID = pvar.productID
114JOIN productpricing as pprice
115 ON pprice.productID = p.productID
116JOIN colors
117 ON colors.colorID = pvar.colorID
118JOIN sizes
119 ON sizes.sizeID = pvar.sizeID
120JOIN product_variant_images as pvari
121 ON pvari.productvariantID = pvar.productvariantID
122JOIN images
123 ON images.imageID = pvari.imageID
124WHERE p.productID = 1 AND pvar.isActive = 1 AND NOW() BETWEEN pprice.startDate AND pprice.endDate;
125
126# productID, name, longDescription, shortDescription, color, size, quantity, price, imageFilename
127'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '50', '2', '309.99', 'shirtwhite1.jpg'
128'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '50', '2', '309.99', 'shirtwhite2.jpg'
129'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '56', '5', '309.99', 'shirtwhite1.jpg'
130'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'White', '56', '5', '309.99', 'shirtwhite2.jpg'
131'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'Blue', '56', '1', '309.99', 'shirtblue1.jpg'
132'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', 'Blue', '56', '1', '309.99', 'shirtblue2.jpg'
133
134SELECT
135 p.productID,
136 p.name,
137 p.longDescription,
138 p.shortDescription,
139 pprice.price
140FROM products as p
141JOIN productpricing as pprice
142 ON pprice.productID = p.productID
143WHERE p.productID = 1 AND NOW() BETWEEN pprice.startDate AND pprice.endDate;
144
145SELECT
146 c.color,
147 s.size,
148 pvar.quantity
149FROM product_variants as pvar
150JOIN colors AS c
151 ON c.colorID = pvar.colorID
152JOIN sizes as s
153 ON s.sizeID = pvar.sizeID
154WHERE pvar.productID = 1;
155
156SELECT DISTINCT
157 c.color,
158 i.imageFilename
159FROM images AS i
160JOIN product_variant_images as pvari
161 ON pvari.imageID = i.imageID
162JOIN product_variants as pvar
163 ON pvar.productvariantID = pvari.productvariantID
164JOIN colors AS c
165 ON c.colorID = pvar.colorID
166WHERE pvar.productID = 1;
167
168# productID, name, longDescription, shortDescription, price
169'1', 'A shirt', 'Long description of this product', 'shortDesc of shirt', '309.99'
170
171# color, size, quantity
172'White', '50', '2'
173'Blue', '56', '1'
174'White', '56', '5'
175
176# color, imageFilename
177'White', 'shirtwhite1.jpg'
178'White', 'shirtwhite2.jpg'
179'Blue', 'shirtblue1.jpg'
180'Blue', 'shirtblue2.jpg'