DB Design - Inventory with expiring products

[needs_upgrade]

Hi guys. My client has been using the system i made him for months now. But he realized he has to add new products having expiration dates. So i have to update my tables design.  These are my current tables.

 

CREATE TABLE `branch_products` (
  `bp_id` int( NOT NULL AUTO_INCREMENT,
  `branch_id` tinyint(4) NOT NULL,
  `product_id` int(5) NOT NULL,
  `stocks` int(10) NOT NULL,
  `reorder_level` int(6) NOT NULL,
  `ave_acquisition` decimal(10,2) NOT NULL,
  `last_movement` date NOT NULL,
  PRIMARY KEY (`bp_id`)
) ENGINE=MyISAM;


CREATE TABLE `purchases` (
  `purchase_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `branch_id` int(2) NOT NULL,
  `or_num` varchar(10) COLLATE latin1_general_ci NOT NULL,
  `supplier_id` int(4) unsigned NOT NULL,
  `delivery` date NOT NULL DEFAULT '0000-00-00',
  `received_by` int(2) NOT NULL,
  `notes` varchar(255) COLLATE latin1_general_ci NOT NULL,
  `line_total` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  `balance` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`purchase_id`)
) ENGINE=MyISAM;


CREATE TABLE IF NOT EXISTS `purchase_details` (
  `pd_id` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `purchase_id` int(10) unsigned NOT NULL,
  `product_id` int(5) unsigned NOT NULL,
  `quantity` int(6) unsigned NOT NULL,
  `unit_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `net_price` decimal(13,2) NOT NULL,
  PRIMARY KEY (`pd_id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `sales` (
  `sale_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `or_num` int(6) unsigned zerofill NOT NULL,
  `branch_id` tinyint(4) NOT NULL,
  `customer_id` int(4) unsigned NOT NULL DEFAULT '0',
  `sale_dtime` datetime NOT NULL,
  `due_date` date NOT NULL,
  `notes` text COLLATE latin1_general_ci NOT NULL,
  `line_total` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  `balance` decimal(13,2) unsigned NOT NULL DEFAULT '0.00',
  `revenue` decimal(10,2) NOT NULL,
  PRIMARY KEY (`sale_id`)
) ENGINE=MyISAM;

CREATE TABLE IF NOT EXISTS `sale_details` (
  `sd_id` int(15) unsigned NOT NULL AUTO_INCREMENT,
  `sale_id` int(10) unsigned NOT NULL,
  `product_id` int(5) unsigned NOT NULL,
  `acq_price` decimal(10,2) NOT NULL,
  `quantity` int(6) unsigned NOT NULL,
  `price` decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  `net_price` decimal(13,2) NOT NULL,
  PRIMARY KEY (`sd_id`)
) ENGINE=MyISAM;

 

What fields should i add to existing tables, and what new tables should i make to be able to handle if my client wants to know how many product 'A' are there in branch '1' that will expire on sept 30, 2011? Thanks so much guys.