Discount codes
Discount codes are a great way to both entice new customers into a store, and also to help retain customers with special discounts. The discount code should work by allowing the customer to enter a code, which will then be verified by the store, and then a discount will be applied to the order.
The following are discount options we may wish to have available in our store:
- A fixed amount deducted from the cost of the order
- A fixed percentage deducted from the cost of the order
- The shipping cost altered, either to free or to a lower amount
- Product-based discounts (although we won’t cover this one in the article)
It may also be useful to take into account the cost of the customer’s basket; after all if we have a $5 discount code, we probably wouldn’t want that to apply for orders of $5 or lower, and may wish to apply a minimum order amount.
Discount codes data
When storing discount codes in the framework, we need to store and account for:
- The voucher code itself, so that we can check that the customer is entering a valid code
- Whether the voucher code is active, as we may wish to prepare some voucher codes, but not have them usable until a certain time, or we may wish to discontinue a code
- A minimum value for the customer’s basket, either as an incentive for the customer to purchase more or to prevent loss-making situations (for example a $10 discount on a $5 purchase!)
- The type of discount:
- Percentage: To indicate that the discount amount is a percentage to be removed from the cost
- Fixed amount deducted: To indicate that the discount amount is a fixed amount to be removed from the order total
- Fixed amount set to shipping: To indicate that the discount amount is to be the new value for the shipping cost
- Discount amount; that is, the amount of discount to be applied
- The number of vouchers issued, if we wish to limit the number of uses of a particular voucher code
- An expiry date, so that if we wish to have the voucher code expire, codes with a date after the stored expiry date would no longer work
Discount codes database
The following table illustrates this information as database fields within a table:
The default value for num_vouchers is -1, which we will use for vouchers that are not limited to a set number of issues.
|
Field |
Type |
Description |
|
ID |
Integer (Primary Key, Auto increment) |
For the framework to reference the code |
|
Vouchercode |
Varchar |
The code the customer enters into the order |
|
Active |
Boolean |
If the code can be used |
|
Min_basket_cost |
Float |
The minimum cost of the customer’s basket for the code to work for them |
|
Discount_operation |
ENUM(‘-‘,%’,’s’) |
The type of discount |
|
Num_vouchers |
Integer |
Number of times the voucher can be used |
|
Expiry |
timestamp |
The date the voucher code expires, and is no longer usable |
The following code represents this data in our database:
CREATE TABLE `discount_codes` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`vouchercode` VARCHAR( 25 ) NOT NULL ,
`active` TINYINT( 1 ) NOT NULL ,
`min_basket_cost` FLOAT NOT NULL ,
`discount_operation` ENUM( '-', '%', 's' ) NOT NULL ,
`discount_amount` FLOAT NOT NULL ,
`num_vouchers` INT( 11 ) NOT NULL DEFAULT '-1',
`expiry` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
PRIMARY KEY ( `ID` )
) ENGINE = INNODB DEFAULT CHARSET = latin1 AUTO_INCREMENT =1;
