Inventory System Design?

[jbennet]

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?

[pritaeas]

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 ?

[fdtoo]

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!

[andrew_onsite]

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;

[asaukani]

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

[Netcode]

Shouldn't the ProductCategory have an Id and Description

I totally agree with you