Which function architecture is the best for maintainability in this simple case?

Question

I'm adding "promo code" functionality to an online shopping cart written by someone else. This is a pretty micky mouse architecture question, but I would like to hear a couple of opinions. All three of my ideas would work, but which do you think is best for maintainability?

So, here's the basic table structure for the promo codes:

CREATE TABLE `promocodes` (
    `promocode_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    `promocode` VARCHAR( 255 ) NOT NULL ,
    `description` VARCHAR( 255 ) NULL ,
    `discount_percentage` INT NULL ,
    `discount_dollars` DECIMAL( 6, 2 ) NULL ,
    `expiration_date` DATE NULL ,
    `uses_remaining` INT NULL ,
    `is_active` BOOL NOT NULL DEFAULT '1'
)

What's the smartest (without getting overcomplicated):

Check existence with SQL, everything else seperately in code

// LIBRARY
function promoCodeExists($promoCodeString){
  // make sql call
  return $promoCodeDetailsHash; // or false if no record
}

function isPromoCodeUsable($promoCodeDetailsHash){
  // check expiry date and number of uses left and active / inactive
}

function usePromoCode($promoCodeId){
  // do order association
  // decrement uses left
}

// USAGE 
$promoCodeDetailsHash = promoCodeExists($promoCode);
if (is_array($promoCodeDetailsHash) AND isPromoCodeUsable($promoCodeDetailsHash)){
  usePromoCode($promoCodeDetailsHash['id'])
} else {
  // invalid promo code
}

Or, have a validation function but have it called only by the get function:

// LIBRARY
function validatePromoCode($promoCodeDetailsHash){
  // check expiry date and number of uses left and active / inactive
}

function isPromoCodeUsable($promoCodeString){
  // make sql call
  return validatePromoCode($promoCodeDetailsHash); // or false if no record
}

// USAGE 
$promoCodeDetailsHash = promoCodeExists($promoCode);
if (is_array(isPromoCodeUsable($promoCodeDetailsHash))){
  usePromoCode($promoCodeDetailsHash['id'])
} else {
  // invalid promo code
}

Check everything in SQL with invalid the same as nonexistance:

// LIBRARY
function getDetailsForUsablePromoCode($promoCode){
  // use SQL WHERE clauses to only return existence for currently valid promo codes
  // or false if no result
}

// USAGE
$promoCodeDetailsHash = getDetailsForUsablePromoCode($promoCode)
if (is_array($promoCodeDetailsHash)){
  usePromoCode($promoCodeDetailsHash['id'])
} else {
  // error state
}

Feel free to point out any other approaches or wackness here.

Answer 1

In my application, I create it as 2 table. First table just like you, but only hold the usage_limit as integer. In second table, I will hold the usage, one usage per row.

In the promocode_usage table, I will have promocode_id as foreign key, and other required column such as usage datetime, user id, etc. To check if the promo is still available, I will simply count the row in promocode_usage table that have promocode_id I want to check. If the result less than usage_limit value, the promo can be used.