Hi,

I am creating a B2B e-commerce site, built around a catalog of products. Categories are 3 levels deep maximum.




I need to implement a discount policy based on users. Discounts can be applied on categories, subcategories or products. Discounts are cascading down from categories to products.



For example here are some price policies for one customer :



1) I set 10% off on any product part of Input device



» cat. Input device -------- (10%)



š sub categories and products inherit this discount



» cat. mouse ------------ (10%)

- prod. Mouse1 ---(10%)

- prod. Mouse2 ---(10%)

- prod. Mouse3 ---(10%)

» cat. Keyboard ---(10%)

- prod. Keyboard1 ---(10%)

- prod. Keyboard1 ---(10%)

- prod. Keyboard1 ---(10%)

» cat. Camera ---(10%)

- prod. Camera1 ---(10%)

- prod. Camera2 ---(10%)





2) Then I decide to set 15% off on any product part of Keyboard category



» cat. Input device -------- (10%)

» cat. Input device -------- (10%)

» cat. mouse ------------ (10%)

- prod. Mouse1 ---(10%)

- prod. Mouse2 ---(10%)

- prod. Mouse3 ---(10%)

» cat. Keyboard ---(15%)

=> sub categories and products inherit this discount

- prod. Keyboard1 ---(15%)

- prod. Keyboard1 ---(15%)

- prod. Keyboard1 ---(15%)

» cat. Camera ---(10%)

- prod. Camera1 ---(10%)

- prod. Camera2 ---(10%)



3) And then I decide to set only 5% off on camera2 product



» cat. Input device -------- (10%)

» cat. mouse ------------ (10%)

- prod. Mouse1 ---(10%)

- prod. Mouse2 ---(10%)

- prod. Mouse3 ---(10%)

» cat. Keyboard ---(15%)

- prod. Keyboard1 ---(15%)

- prod. Keyboard1 ---(15%)

- prod. Keyboard1 ---(15%)

» cat. Camera ---(10%)

- prod. Camera1 ---(10%)

- prod. Camera2 ---(5%)



To implement this, based on the very cool Rudy Limeback’s advice found in this forum and others



http://www.sitepoint.com/forums/showthread.php?t=163425

http://searchdatabase.techtarget.com...285649,00.html



I’ve created 2 one-to-many tables: userproductdiscount and usercategoriediscount

And then used a sql query to get the discount applied to each product in a specific category in order to display a product list with catalog price and a “your price” price based on the discount.



As it is the first time I do something like that I would appreciate to know if this is a good way to do this kind of thing? Is there any other way? Do you know some tutorials about it?



Thanks







The simplified database structure :





Tables for the Catalogue

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





Code:
 

CREATE TABLE `categories_ctg` (

  `id_ctg` int(11) NOT NULL auto_increment,

  `idctg_ctg` int(11) default NULL,

  `name_ctg` varchar(100) NOT NULL default '',

  PRIMARY KEY  (`id_ctg`),

  UNIQUE KEY `unique_name` (`idctg_ctg`,`name_ctg`),

  KEY `idctg_ctg` (`idctg_ctg`),

  KEY `order_ctg` (`order_ctg`)

) TYPE=MyISAM AUTO_INCREMENT=67 ;





CREATE TABLE `products_prd` (

  `id_prd` int(11) NOT NULL auto_increment,

  `idctg_prd` int(11) NOT NULL default '0',

  `idman_prd` int(11) NOT NULL default '0',

  `name_prd` varchar(200) NOT NULL default '',

  `sku_prd` varchar(255) NOT NULL default '',

  `price_prd` float NOT NULL default '0',

  PRIMARY KEY  (`id_prd`),

  UNIQUE KEY `product name` (`idctg_prd`,`name_prd`),

  KEY `offer_prd` (`offer_prd`),

  KEY `idctg_prd` (`idctg_prd`),

  KEY `idman_ctg` (`idman_prd`),

  KEY `sku_prd` (`sku_prd`),

  FULLTEXT KEY `description_prd` (`description_prd`)

) TYPE=MyISAM AUTO_INCREMENT=80 ;




Table for the Customers

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



Code:
 

CREATE TABLE `users_usr` (

  `id_usr` int(11) NOT NULL auto_increment,

  `email_usr` varchar(100) NOT NULL default '',

  `firstname_usr` varchar(255) NOT NULL default '',

  `lastname_usr` varchar(255) NOT NULL default '',

  `password_usr` varchar(32) NOT NULL default '',

  `level_usr` int(11) NOT NULL default '0',

  PRIMARY KEY  (`id_usr`),

  UNIQUE KEY `email_usr` (`email_usr`,`firstname_usr`),

  KEY `password_usr` (`password_usr`),

  KEY `level_usr` (`level_usr`)

) TYPE=MyISAM AUTO_INCREMENT=8 ;




Tables for the Discount policies




Code:
CREATE TABLE `userproductdiscount` (  `id_upd` int(10) unsigned NOT NULL auto_increment,  `idprd_upd` int(10) unsigned NOT NULL default '0',  `idusr_upd` int(10) unsigned NOT NULL default '0',  `discount_upd` int(11) NOT NULL default '0',  PRIMARY KEY  (`id_upd`)) TYPE=MyISAM AUTO_INCREMENT=1 ;CREATE TABLE `usercategoriediscount` (  `id_ucd` int(10) unsigned NOT NULL auto_increment,  `idprd_ucd` int(10) unsigned NOT NULL default '0',  `idusr_ucd` int(10) unsigned NOT NULL default '0',  `discount_ucd` int(11) NOT NULL default '0',  PRIMARY KEY  (`id_upd`)) TYPE=MyISAM AUTO_INCREMENT=1 ;




Then the SQL QUERY base on usrid=1 and categorie id = 47

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



Code:
 

SELECT usr.email_usr, name_prd,  price_prd, upd.discount_upd,  ucd0.discount_ucd, ucd1.discount_ucd, ucd2.discount_ucd, ctg0.id_ctg



FROM `products_prd` prd, users_usr usr



LEFT OUTER JOIN userproductdiscount_upd upd
ON upd.idprd_upd = prd.id_prd



LEFT OUTER JOIN categories_ctg ctg0
ON prd.idctg_prd = ctg0.id_ctg 



LEFT OUTER JOIN usercategoriediscount_ucd ucd0
ON ucd0.idctg_ucd = ctg0.id_ctg



LEFT OUTER JOIN categories_ctg ctg1
ON ctg0.idctg_ctg = ctg1.id_ctg



LEFT OUTER JOIN usercategoriediscount_ucd ucd1
ON ucd1.idctg_ucd = ctg1.id_ctg



LEFT OUTER JOIN categories_ctg ctg2
ON ctg1.idctg_ctg = ctg2.id_ctg



LEFT OUTER JOIN usercategoriediscount_ucd ucd2
ON ucd2.idctg_ucd = ctg2.id_ctg



WHERE usr.id_usr=1 AND ctg0.id_ctg = 47;