DB design problem

[needs_upgrade]

I'm sorry for the previous post. I pressed the post button instead of preview while I'm typing the description. So here's the completed post.

 

I'm having difficulty in designing my database for a multi-level distribution system. A distributor receives stocks from a supplier. A distributor has one or many dealers. A dealer has one or many retailers. For example the supplier issues a batch of product X to the supplier. Let's call it Product X Batch One (1), with serial numbers xxxx1 - x9999, expiring on YYYY-MM-DD. Then the distributor issued the batch one s/n xxxx1 - xx999 to dealer "A", s/n x1000 - x1999 to dealer "B" and s/n x2000 - x2999 to dealer "C". Then the dealers will distribute them to their retailers. My database design for my inventory is as follows:

CREATE TABLE stocks (
   stock_id int(10) NOT NULL AUTO_INCREMENT,
   product_id int(3) NOT NULL,
   batch_id int(6) NOT NULL,
   expire date NOT NULL,
   quantity int(6) NOT NULL,
   capital decimal(10,2) NOT NULL,
   PRIMARY KEY (stock_id)
)

 

What if the distributor wants to trace which serial numbers are still in the inventory and to which dealers are the missing numbers have been issued? How should I design my database? Here's what I have in mind so far:

 

CREATE TABLE serials (
   sn_id int(10) NOT NULL AUTO_INCREMENT,
   stock_id int(10) NOT NULL,
   sn varchar(6) NOT NULL,
   taken int(1),
   PRIMARY KEY (sn_id)
)

 

Please feel free to criticize my table. If you have better suggestions, I would be glad to implement it. Thanks so much guys!

 

Here are my other tables involved in the inventory movements:

CREATE TABLE purchases (
  purchase_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  supplier_id int(4) unsigned NOT NULL,
  delivery date NOT NULL DEFAULT '0000-00-00',
  line_total decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (purchase_id)
)
CREATE TABLE 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,
  expire date NOT NULL,
  unit_price decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`pd_id`)
)
CREATE TABLE sales (
  sale_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  dealer_id int(6) unsigned NOT NULL DEFAULT '0',
  due_date date NOT NULL,
  line_total decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (sale_id)
)
CREATE TABLE 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,
  capital decimal(10,2) NOT NULL,
  quantity int(6) unsigned NOT NULL,
  sell_price decimal(10,2) unsigned NOT NULL DEFAULT '0.00',
  PRIMARY KEY (sd_id)
)