Entity-Relationship Design Problem

[needs_upgrade]

Hello guys.

I'm currently making a warehousing project and im having some difficulties in designing the Entity-Relationship Diagram. To start the problem simple, a company may have one or any warehouses and may have one or many store branches. They also buy and sell one or any products. Basic transactions are as follows. The company purchases one or many products

from a supplier and that purchase is stored in a particular warehouse. A product or products is/are then dispensed from a particular warehouse to a particular store branch. Then a product or products is/are then sold from a particular store branch to a particular customer.

 

Here are my tables:

 

--

-- Simplified table structure for table `purchases` - purchased product/s from supplier to a warehouse

--

CREATE TABLE IF NOT EXISTS `purchases` (

  `purchase_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `whouse_id` int(2) NOT NULL,

  `supplier_id` int(4) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`purchase_id`)

) ENGINE=MyISAM ;

--

-- Table structure for table `purchase_details`

--

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(4) unsigned NOT NULL,

  `quantity` int(6) unsigned NOT NULL,

  `balance` int(6) NOT NULL,

  `unit_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.000',

  `unit_discount` decimal(8,2) unsigned NOT NULL DEFAULT '0.000',

  PRIMARY KEY (`pd_id`)

) ENGINE=MyISAM ;

 

 

 

--

-- Table structure for table `dispenses` - dispensed product/s from warehouse to store branch

--

CREATE TABLE IF NOT EXISTS `dispenses` (

  `dispense_id` int(15) unsigned NOT NULL AUTO_INCREMENT,

  `whouse_id` int(2) NOT NULL,

  `branch_id` int(2) NOT NULL,

  `dispense_date` date NOT NULL,

  PRIMARY KEY (`dispense_id`)

) ENGINE=MyISAM ;

--

-- Table structure for table `dispense_details`

--

CREATE TABLE IF NOT EXISTS `dispense_details` (

  `dd_id` int(15) unsigned NOT NULL AUTO_INCREMENT,

  `dispense_id` int(15) NOT NULL,

  `pd_id` int(15) NOT NULL,

  `product_id` int(4) NOT NULL,

  `quantity` int(6) NOT NULL,

  `balance` int(6) NOT NULL,

  PRIMARY KEY (`dd_id`)

) ENGINE=MyISAM ;

 

 

 

--

-- Simplified table structure for table `sales` - sold product/s from store branch to customer

--

CREATE TABLE IF NOT EXISTS `sales` (

  `sale_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `customer_id` int(4) unsigned NOT NULL DEFAULT '0',

  `user_id` int(2) unsigned NOT NULL DEFAULT '0',

  PRIMARY KEY (`sale_id`)

) ENGINE=MyISAM ;

--

-- Table structure for table `sale_details`

--

CREATE TABLE IF NOT EXISTS `sale_details` (

  `sd_id` int(15) unsigned NOT NULL AUTO_INCREMENT,

  `sale_id` int(10) unsigned NOT NULL,

  `dd_id` int(15) unsigned NOT NULL,

  `product_id` int(5) unsigned NOT NULL,

  `quantity` int(6) unsigned NOT NULL,

  `acq_price` decimal(10,2) NOT NULL,

  `sell_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.000',

  `sell_discount` decimal(8,2) unsigned NOT NULL DEFAULT '0.000',

  PRIMARY KEY (`sale_detail_id`)

) ENGINE=MyISAM ;

 

 

My problem now is that there are instances of product/s transfersred from one warehouse to another warehouse because some products are fast-moving in a warehouse but are slow-moving warehouse. the same instances also happen on the branch to branch level. how should i edit my existing tables to support the problem? Please feel free to criticize my design. If you have better designs, I'll would be glad to consider and use them.

 

These are what I have in mind:

 

--

-- Updated table structure for table `purchases`

--

CREATE TABLE IF NOT EXISTS `purchases` (

  `purchase_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

  `fr_whouse_id` int(2) NOT NULL COMMENT 'non-zero if transferred from another warehouse; 0 otherwise',

  `to_whouse_id` int(2) NOT NULL COMMENT 'value is always non-zero',

  `supplier_id` int(4) unsigned NOT NULL DEFAULT '0' COMMENT '0 if transferred from another warehouse; non-zero otherwise',

  PRIMARY KEY (`purchase_id`)

) ENGINE=MyISAM ;

--

-- Updated table structure for table `purchase_details`

--

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(4) unsigned NOT NULL,

  `quantity` int(6) unsigned NOT NULL,

  `balance` int(6) NOT NULL,

  `unit_price` decimal(10,2) unsigned NOT NULL DEFAULT '0.000',

  `unit_discount` decimal(8,2) unsigned NOT NULL DEFAULT '0.000',

  PRIMARY KEY (`pd_id`)

) ENGINE=MyISAM ;

 

 

 

--

-- Updated table structure for table `dispenses` - dispensed product/s from warehouse to store branch

--

CREATE TABLE IF NOT EXISTS `dispenses` (

  `dispense_id` int(15) unsigned NOT NULL AUTO_INCREMENT,

  `whouse_id` int(2) NOT NULL  COMMENT '0 if transferred from another branch; non-zero otherwise',

  `from_branch_id` int(2) NOT NULL COMMENT 'non-zero if transferred from another branch; 0 otherwise',

  `to_branch_id` int(2) NOT NULL COMMENT 'value always non-zero',

  `dispense_date` date NOT NULL,

  PRIMARY KEY (`dispense_id`)

) ENGINE=MyISAM ;

--

-- Table structure for table `dispense_details`

--

CREATE TABLE IF NOT EXISTS `dispense_details` (

  `dd_id` int(15) unsigned NOT NULL AUTO_INCREMENT,

  `dispense_id` int(15) NOT NULL,

  `pd_id` int(15) NOT NULL,

  `product_id` int(4) NOT NULL,

  `quantity` int(6) NOT NULL,

  `balance` int(6) NOT NULL,

  PRIMARY KEY (`dd_id`)

) ENGINE=MyISAM ;

 

 

I'm hoping for your replies. Thank you guys.