+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: eCommerce Discount Groups (/showthread.php?tid=43874)
eCommerce Discount Groups - El Forum - 07-26-2011
[eluser]atw[/eluser]
Hi,
I have recently started development on an internal project for my employer (an eCommerce system).
I can handle most of the system, however, the complication that I knew was going to cause me problems (but has turned out way more difficult than expected) is discounts.
This shop will sell Wines, Spirits and Beers etc. My boss wants to be able to offer:
Buy [x] of [this RANGE of products] and get [x] of [other OR same RANGE of products] AND {possibly} if the cart is close to a discount threshold, inform the user that buying 2 more (or whatever) will get them a better bargain...
I have taken this to be a many to many relationship and set up the database as in the MySQL Dump at the bottom of this post.
My question is, how should I approach comparing a user’s cart against my database’s groups and discount tolerances? And is my schema suitable?
Code:
CREATE TABLE IF NOT EXISTS `product` (
`product_id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`product_name` varchar(255) DEFAULT NULL,
`product_description` text,
`source_price` decimal(10,2) DEFAULT NULL,
`source_pack_size` int(11) DEFAULT NULL,
`source_item_size` decimal(10,2) DEFAULT NULL,
`source_unit_id` int(11) NOT NULL,
`resale_price` decimal(10,2) DEFAULT NULL,
`resale_pack_size` int(11) DEFAULT NULL,
`percentage_return` decimal(10,2) DEFAULT NULL,
`product_code` varchar(64) DEFAULT NULL,
`current_stock` int(11) NOT NULL DEFAULT '0',
`product_stock_level` int(11) DEFAULT NULL,
`product_featured` enum('y','n') NOT NULL DEFAULT 'n',
`product_active` enum('y','n') NOT NULL DEFAULT 'y',
`so_threshold` int(11) DEFAULT NULL,
`so_product_id` int(11) DEFAULT NULL,
`so_product_qty` int(11) DEFAULT NULL,
`so_discount_percentage` tinyint(3) DEFAULT NULL,
`so_offer_type` enum('none','bogof','precent') DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`modified_at` datetime DEFAULT NULL,
PRIMARY KEY (`product_id`),
KEY `FI_within` (`category_id`),
KEY `FI_item_size` (`source_unit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1589 ;
CREATE TABLE IF NOT EXISTS `product_groups` (
`product_group_id` int(11) NOT NULL AUTO_INCREMENT,
`product_group_name` varchar(100) NOT NULL,
`so_threshold` int(11) DEFAULT NULL,
`so_group_id` int(11) DEFAULT NULL,
`so_group_qty` int(11) DEFAULT NULL,
`product_group_created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`product_group_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;
CREATE TABLE IF NOT EXISTS `product_product_groups` (
`product_id` int(11) NOT NULL,
`product_group_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;Many thanks,
Andy