Jump to content

Recommended Posts

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.

 

Link to comment
https://forums.phpfreaks.com/topic/185700-entity-relationship-design-problem/
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.