1,105,417 Community Members

Inventory System Design?

Member Avatar
jbennet
Moderator
17,127 posts since Apr 2005
Reputation Points: 1,618 [?]
Q&As Helped to Solve: 736 [?]
Skill Endorsements: 38 [?]
Team Colleague
Featured
 
0
 

Can anyone help me with an Inventory System Design?

Its got to track the location of items in the warehouse (and shop floor) for stocktake/empty peg etc... It has to handle refunds and sales.

So far ive come up with:

Product (ProductID, Name, ProductCategory,Description,Price,SupplierID)
ProductCategory(ProductCategory)
Location (LocationID, Description)
ProductLocation (ProductID, LocationID, Quantity)
Staff (StaffID,StaffName,PIN)
Customer (CustomerID, CustomerName,CustomerAddress)
Transaction (TransactionID,StaffID,CustomerID,Timestamp) - Negatives for refunds?
TransactionLine (TransactionID,ProductID,Price,Quantity)
CustomerDelivery(DeliveryID,TransactionID,CustomerID,SignedFor)
Supplier (SupplierID,SupplierName,CustomerID)

Further ideas?

Member Avatar
pritaeas
mod_pritaeas
11,317 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,835 [?]
Skill Endorsements: 156 [?]
Moderator
Featured
Sponsor
 
0
 

Shouldn't the ProductCategory have an Id and Description ? I think a refund is just a negative quantity (and thus negative price) in your TransactionLine (doesn't it need it's own Id ?). Perhaps you have to think about product ordering, since normally you'd have to order fixed amounts per product. Why is CustomerId in Supplier ?

Member Avatar
fdtoo
Newbie Poster
12 posts since Apr 2006
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

Your previous posted question as per below :

Its got to track the location of items in the warehouse (and shop floor) for stocktake/empty peg etc... It has to handle refunds and sales.

So far ive come up with:

Product (ProductID, Name, ProductCategory,Description,Price,SupplierID)
ProductCategory(ProductCategory)
Location (LocationID, Description)
ProductLocation (ProductID, LocationID, Quantity)
Staff (StaffID,StaffName,PIN)
Customer (CustomerID, CustomerName,CustomerAddress)
Transaction (TransactionID,StaffID,CustomerID,Timestamp) - Negatives for refunds?
TransactionLine (TransactionID,ProductID,Price,Quantity)
CustomerDelivery(DeliveryID,TransactionID,CustomerID,SignedFor)
Supplier (SupplierID,SupplierName,CustomerID)

Further ideas?

My Comment :
I think you missed out 2 important fields. The costing mechanism and cost_per_unit fields.You need the costing field to assign the costing valuation method for your stock movement (e.g. fifo/lifo). You need cost_per_unit to monitor and update your unit cost based on the selected costing method. Are you having more than one warehouse location? Any bin number assignment? Do you require serialization for product warranty purposes? You may need to include these fields if your answer is yes. I can't elaborate much here. But do get the book on accounting database design from accountingdes.com website. It covers a detail explanation on the type of fields you would need to create for an inventory system.

Good luck!

Member Avatar
andrew_onsite
Newbie Poster
1 post since Aug 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
-1
 

I have developed a DB design .

Please have a look and let me know any questions.This is developed in MYSQL

-- phpMyAdmin SQL Dump
-- version 3.2.0.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Aug 25, 2011 at 07:43 PM
-- Server version: 5.1.36
-- PHP Version: 5.3.0

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `inventory`
--

-- --------------------------------------------------------

--
-- Table structure for table `branch_info`
--

CREATE TABLE IF NOT EXISTS `branch_info` (
`pk_branch_id` varchar(50) NOT NULL,
`fk_company_id` varchar(50) NOT NULL,
`branch_name` varchar(50) NOT NULL,
`branch_poc` varchar(50) NOT NULL,
`branch_detail` varchar(200) NOT NULL,
`website` varchar(100) NOT NULL,
`created_by` varchar(50) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`last_updated_by` varchar(50) NOT NULL,
`last_updated_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` varchar(1) NOT NULL,
PRIMARY KEY (`pk_branch_id`),
KEY `FK_branch_info_company_info` (`fk_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `branch_info`
--


-- --------------------------------------------------------

--
-- Table structure for table `company_info`
--

CREATE TABLE IF NOT EXISTS `company_info` (
`pk_company_id` varchar(50) NOT NULL,
`company_name` varchar(50) NOT NULL,
`company_type` varchar(25) NOT NULL,
`company_detail` varchar(100) NOT NULL,
`website` varchar(50) NOT NULL,
`created_by` varchar(25) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_by` varchar(50) NOT NULL,
`last_updated_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` varchar(1) NOT NULL,
PRIMARY KEY (`pk_company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `company_info`
--


-- --------------------------------------------------------

--
-- Table structure for table `inventory`
--

CREATE TABLE IF NOT EXISTS `inventory` (
`pk_inventory_id` varchar(50) NOT NULL,
`fk_product_id` varchar(50) NOT NULL,
`fk_company_id` varchar(50) NOT NULL,
`fk_branch_id` varchar(50) NOT NULL,
`quantity` int(5) NOT NULL,
`calculation_unit` varchar(4) NOT NULL,
`io_type` varchar(1) NOT NULL,
`created_by` varchar(25) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_by` varchar(50) NOT NULL,
`last_updated_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` varchar(1) NOT NULL,
PRIMARY KEY (`pk_inventory_id`),
KEY `FK_inventory_product_info` (`fk_product_id`),
KEY `FK_inventory_company_info` (`fk_company_id`),
KEY `FK_inventory_branch_info` (`fk_branch_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `inventory`
--


-- --------------------------------------------------------

--
-- Table structure for table `product_info`
--

CREATE TABLE IF NOT EXISTS `product_info` (
`pk_product_id` varchar(50) NOT NULL,
`product_name` varchar(50) NOT NULL,
`product_detail` varchar(100) NOT NULL,
`model` varchar(50) NOT NULL,
`material_type` varchar(50) NOT NULL,
`quantity` int(5) NOT NULL,
`calculation_unit` varchar(4) NOT NULL,
`current_rate` decimal(8,0) NOT NULL,
`created_by` varchar(25) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_by` varchar(50) NOT NULL,
`last_updated_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` varchar(1) NOT NULL,
PRIMARY KEY (`pk_product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product_info`
--


-- --------------------------------------------------------

--
-- Table structure for table `product_supplier`
--

CREATE TABLE IF NOT EXISTS `product_supplier` (
`pk_product_supplier_id` varchar(101) NOT NULL,
`fk_product_id` varchar(50) NOT NULL,
`fk_supplier_id` varchar(50) NOT NULL,
`created_by` varchar(25) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_by` varchar(50) NOT NULL,
`last_updated_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` varchar(1) NOT NULL,
PRIMARY KEY (`pk_product_supplier_id`),
KEY `FK_product_supplier_product_info` (`fk_product_id`),
KEY `FK_product_supplier_supplier_info` (`fk_supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `product_supplier`
--


-- --------------------------------------------------------

--
-- Table structure for table `supplier_info`
--

CREATE TABLE IF NOT EXISTS `supplier_info` (
`pk_supplier_id` varchar(50) NOT NULL,
`supplier_name` varchar(50) NOT NULL,
`supplier_type` varchar(25) NOT NULL,
`supplier_detail` varchar(100) NOT NULL,
`website` varchar(50) NOT NULL,
`created_by` varchar(25) NOT NULL,
`created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_updated_by` varchar(50) NOT NULL,
`last_updated_datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`status` varchar(1) NOT NULL,
PRIMARY KEY (`pk_supplier_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `supplier_info`
--


--
-- Constraints for dumped tables
--

--
-- Constraints for table `branch_info`
--
ALTER TABLE `branch_info`
ADD CONSTRAINT `FK_branch_info_company_info` FOREIGN KEY (`fk_company_id`) REFERENCES `company_info` (`pk_company_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `inventory`
--
ALTER TABLE `inventory`
ADD CONSTRAINT `FK_inventory_branch_info` FOREIGN KEY (`fk_branch_id`) REFERENCES `branch_info` (`pk_branch_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_inventory_company_info` FOREIGN KEY (`fk_company_id`) REFERENCES `company_info` (`pk_company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_inventory_product_info` FOREIGN KEY (`fk_product_id`) REFERENCES `product_info` (`pk_product_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `product_supplier`
--
ALTER TABLE `product_supplier`
ADD CONSTRAINT `FK_product_supplier_product_info` FOREIGN KEY (`fk_product_id`) REFERENCES `product_info` (`pk_product_id`) ON DELETE CASCADE ON UPDATE CASCADE,
ADD CONSTRAINT `FK_product_supplier_supplier_info` FOREIGN KEY (`fk_supplier_id`) REFERENCES `supplier_info` (`pk_supplier_id`) ON DELETE CASCADE ON UPDATE CASCADE;

Member Avatar
asaukani
Light Poster
44 posts since Mar 2010
Reputation Points: 0 [?]
Q&As Helped to Solve: 9 [?]
Skill Endorsements: 0 [?]
 
0
 

1. i think you need transaction type because negative not only caused by refund but some times of loss, stock adjustment and so on
2, for location may be it should be grouped like warehouse and shop floor

Member Avatar
Netcode
Veteran Poster
1,049 posts since Jun 2009
Reputation Points: 33 [?]
Q&As Helped to Solve: 86 [?]
Skill Endorsements: 8 [?]
 
0
 

Shouldn't the ProductCategory have an Id and Description

I totally agree with you

You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
Tags Related to this Article