1 of 21

� � CMP-3110 ~ E-Commerce Applications Development

Lecture 04

Product Variations and

User Uploads

2 of 21

Giving users choice

  • Many products in e-commerce stores require some sort of choice from the customer
    • Size
    • Color
    • Material

3 of 21

Giving users choice

  • Simple variants
    • single drop-down box of variations of a product

​

  • when we create our database table for items in the shopping basket; it needs only an extra field to record the variant the user is purchasing

​

  • How could this work?
    • Firstly we would need a list of possible variations
    • Secondly we would need to associate these with various products along with any additional cost implications they would have on the product

4 of 21

Giving users choice

5 of 21

Giving users choice

  • Combinations of variants
    • Size
      • Small
      • Medium
      • Large
      • XL
      • XXL
    • Color
      • Red
      • Green
      • Blue
      • Yellow
      • Black
      • White

​

6 of 21

High-level overview�

7 of 21

Database structure

Product Table

Attribute Table

Attribute Value

Product-attribute-value

8 of 21

Attributes table

9 of 21

Attributes table

CREATE TABLE `product_attributes` (

`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR( 50 ) NOT NULL)

ENGINE = INNODB

COMMENT = 'Product Attributes

e.g. Color, Size, etc.';

10 of 21

Attribute values table

11 of 21

Attribute values table

CREATE TABLE `product_attribute_values` (

`ID` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

`name` VARCHAR( 50 ) NOT NULL,

`attribute_id` INT NOT NULL,

INDEX ( `attribute_id` ))

ENGINE = INNODB COMMENT = 'Product Attribute Values

for example Blue, Large, etc.';

12 of 21

Product-attribute-value-association table

13 of 21

Product-attribute-value-association table

CREATE TABLE `product_attribute_value_association` (

`product_id` int(11) NOT NULL,

`attribute_id` int(11) NOT NULL,

`order` int(11) NOT NULL,

`cost_difference` double NOT NULL,

KEY `product_id` (`product_id`,`attribute_id`),

KEY `attribute_id` (`attribute_id`))

ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Association of

products and attribute values';

​

14 of 21

Product-attribute-value-association table

--Constraints for table `product_attribute_value_association`

--

ALTER TABLE `product_attribute_value_association`

ADD CONSTRAINT `product_attribute_value_association_ibfk_2`

FOREIGN KEY (`attribute_id`)

REFERENCES `product_attribute_values` (`ID`)

ON DELETE CASCADE ON UPDATE CASCADE,

ADD CONSTRAINT `product_attribute_value_association_ibfk_1`

FOREIGN KEY (`product_id`) REFERENCES `content` (`ID`)

ON DELETE CASCADE ON UPDATE CASCADE;

15 of 21

Template switching

16 of 21

Giving users control

  • Juniper Theatricals store (Business Rules)
    • Uploading a photograph or image
    • Supplying some custom text

​

  • How to customize a product?
    • Uploads
    • Custom text

17 of 21

Giving users control

18 of 21

Giving users control

  • Maintaining uploads

​

    • If a file is uploaded and a product is added to a basket, and that basket is never converted into an actual order, we will want to remove that file.

​

    • Once an order has been paid for, and processed fully, we would also want to remove it.

​

​

19 of 21

Giving users control

Security considerations

    • Open to abuse from someone repeatedly uploading images.
      • Could implement time delays to prevent this
    • Types of files
      • File uploaded
      • File extension
    • Maximum file size
    • What safeguards are in place to prevent customers finding uploads of other customers?

​

20 of 21

Giving users control

Database changes

    • Extending our products table
      • allow_upload (Boolean)
      • custom_text_inputs (longtext)

​

ALTER TABLE `content_types_products`

ADD `allow_upload` BOOL NOT NULL,

ADD `custom_text_inputs` LONGTEXT NOT NULL ;

21 of 21

Shopping