Open phpMyAdmin, and execute the following code, which creates and populates the attribute table:
-- Create attribute table (stores attributes such as Size and Color)
CREATE TABLE `attribute` (
`attribute_id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) NOT NULL, -- E.g. Color, Size
PRIMARY KEY (`attribute_id`)
) ENGINE=MyISAM;
-- Populate attribute table
INSERT INTO `attribute` (`attribute_id`, `name`) VALUES
(1, 'Size'), (2, 'Color');
2. Continue by creating and populating the attribute_value table, using the following SQL code:
-- Create attribute_value table (stores values such as Yellow or XXL)
CREATE TABLE `attribute_value` (
`attribute_value_id` INT NOT NULL AUTO_INCREMENT,
`attribute_id` INT NOT NULL, -- The ID of the attribute
`value` VARCHAR(100) NOT NULL, -- E.g. Yellow
PRIMARY KEY (`attribute_value_id`),
KEY `idx_attribute_value_attribute_id` (`attribute_id`)
) ENGINE=MyISAM;
CHAPTER 6 ?– PRODUCT ATTRIBUTES 177
-- Populate attribute_value table
INSERT INTO `attribute_value` (`attribute_value_id`, `attribute_id`, `value`)
VALUES
(1, 1, 'S'), (2, 1, 'M'), (3, 1, 'L'), (4, 1, 'XL'), (5, 1, 'XXL'),
(6, 2, 'White'), (7, 2, 'Black'), (8, 2, 'Red'), (9, 2, 'Orange'),
(10, 2, 'Yellow'), (11, 2, 'Green'), (12, 2, 'Blue'),
(13, 2, 'Indigo'), (14, 2, 'Purple');
3. Create and populate the product_attribute table, using the code in the following listing:
-- Create product_attribute table (associates attribute values to products)
CREATE TABLE `product_attribute` (
`product_id` INT NOT NULL,
`attribute_value_id` INT NOT NULL,
PRIMARY KEY (`product_id`, `attribute_value_id`)
) ENGINE=MyISAM;
-- Populate product_attribute table
INSERT INTO `product_attribute` (`product_id`, `attribute_value_id`)
SELECT `p`.
Pages:
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278