Promotion handling

[petermichaux]

Hi,

 

I'm playing with the way that the catalog and promotions work in osCommerce. I'm a little stumped. There are so many types possible types of promotions/sales/discounts/specials that right now this seems to be the most complicated part of developing the database tables.

 

1) 50% off

2) save $3.50

3) two for one

4) buy 3 get one free

5) buy one and get the second half price

 

Promotions 1-5 are intra-product discounts meaning only one product (ie one SKU) is involved. These are all equivalent to a minimum quantity and a percent discount. However the different ways of displaying these promotions is an important choice to store owners.

 

6) buy any three pairs of jeans and get a free belt

7) buy any two t-shirt and get a second t-shirt half price

8) orders over $100 receive 20% off

 

Promotions 6-8 are inter-product promotions (more than one SKU is involved). I think these are much harder to deal with. Promotions 6-7 could be considered intra-category if all products in a category qualify to contribute to the promotion.

 

Some of these promotions (3,4,5,6,7,8) could be considered "dangling carrots" meaning after a certain purchase quantity or dollar value threshold is reached a reward is given.

 

I would like to find a way to handle promotions in a very maintainable and expandable way.

 

I have studied the Elastic Path database schema (http://www.elasticpath.com/ecommerce/docs/4-0/). In Elastic Path, promotions (types 1,2,8) can be set for categories, products or product variations. There is a nice feature that allows the store owner to set a promotion to a category (or product) and then all sub-categories, products and product variations below can inherit the promotion if each is set to do so. However sub-categories etc must inherit all or none of the promotions at higher levels. I don't like how they actually implement the promotions tables and if more promotion types were implemented the number of promotion tables would grow quickly. Another feature I don't like is that I think ever time a product is displayed to the consumer the application must search up the catalog tree for any applicable promotions. That seems like a very inefficient way to do it but maybe there is no better way to do it and handle promotion types 6-8.

 

Here is a simplified version of the catalog tables (without different languages, currencies, customer groups and other complexities).

 

# categories with parent_category_id==0 are top level departments

# Example: Running shoes

# Example: Adidas (a sub category of Running shoes)

CREATE TABLE category (

category_id INTEGER(10) UNSIGNED NOT NULL auto_increment,

parent_category_id int(10) unsigned NOT NULL default '0',

inherit_promotions BOOLEAN NOT NULL default '0',

PRIMARY KEY (category_id)

) ENGINE=innoDB;

 

# Example: Adidas Trail Response

CREATE TABLE product (

product_id INTEGER(10) UNSIGNED NOT NULL auto_increment,

inherit_promotions BOOLEAN NOT NULL default '0',

PRIMARY KEY (product_id)

) ENGINE=innoDB;

 

# a product can be in multiple categories

CREATE TABLE category_product (

category_id INTEGER(10) UNSIGNED NOT NULL default '0',

product_id INTEGER(10) UNSIGNED NOT NULL default '0',

PRIMARY KEY (category_id, product_id)

) ENGINE=innoDB;

 

# Example: Adidas Trail Response, dark blue, size 14, SKU 5575877

CREATE TABLE product_variation (

product_variation_id INTEGER(10) UNSIGNED NOT NULL auto_increment,

product_id INTEGER(10) UNSIGNED NOT NULL default '0',

sku INTEGER(10) UNSIGNED NOT NULL default '0',

inherit_promotions BOOLEAN NOT NULL default '0',

PRIMARY KEY (product_variation_id)

) ENGINE=innoDB;

 

# these are price tiers to enable discounted volume purchasing

CREATE TABLE price (

product_variation_id int(10) unsigned NOT NULL,

quantity int(10) unsigned NOT NULL default '1',

price decimal(10,2) NOT NULL,

PRIMARY KEY (product_variation_id, quantity)

) ENGINE=INNODB;

 

####################################################

# Out of the many promotion tables I've played with here are some

# These are not necessarily good

####################################################

 

CREATE TABLE promotion (

promotion_id int(10) unsigned NOT NULL auto_increment,

start_date datetime NOT NULL,

end_date datetime NOT NULL,

promo_code varchar(20) NOT NULL,

discount decimal (10, 2) NOT NULL,

type integer(5) unsigned NOT NULL default '1', #1=percentage, 2=fixed

PRIMARY KEY (promotion_id)

) ENGINE=INNODB;

 

CREATE TABLE promotion_category (

promotion_id int(10) unsigned NOT NULL,

category_id int(10) unsigned NOT NULL,

PRIMARY KEY (promotion_id, category_id)

) ENGINE=INNODB;

 

CREATE TABLE promotion_product (

promotion_id int(10) unsigned NOT NULL,

product_id int(10) unsigned NOT NULL,

PRIMARY KEY (promotion_id, product_id)

) ENGINE=INNODB;

 

CREATE TABLE promotion_product_variation (

promotion_id int(10) unsigned NOT NULL,

product_variation_id int(10) unsigned NOT NULL,

PRIMARY KEY (promotion_id, product_variation_id)

) ENGINE=INNODB;

 

CREATE TABLE promotion_price (

product_variation_id int(10) unsigned NOT NULL,

promotion_id int(10) unsigned NOT NULL,

quantity int(10) unsigned NOT NULL default '1',

price decimal(10,2) NOT NULL,

PRIMARY KEY (product_variation_id, promotion_id, quantity)

) ENGINE=INNODB;

 

 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

 

What about "limit 2 per customer" loss leader type products? Ahhhhhhhhhhhh! I think ignore these!