Results 1 to 1 of 1
Jun 19, 2008, 02:00 #1
- Join Date
- Jun 2008
- 0 Post(s)
- 0 Thread(s)
Any db design of FIFO for inventory control
I am asked to do a job that's creating fifo (first in first out) for inventory control. There are many kinds of transaction such as: Goods purchasing, Return Goods Purchased, Moving Goods from one warehouse to another one, change the goods name (like from fresh tomatoes to rotten tomatoes), Selling (Invoice) and Credit Invoice (Return for Sales).
I was so confused and I did a table like this:
CREATE TABLE IF NOT EXISTS `fifotrans` ( `id` int(11) NOT NULL auto_increment, `fifotransferid` int(11) NOT NULL default '0' COMMENT 'this trans is from another fifo transaction if <> 0', `transid` int(11) NOT NULL, `bbmid` int(11) default '0', `transtype` varchar(10) NOT NULL, `transsigned` tinyint(4) NOT NULL default '1', `transdate` date NOT NULL, `goodsid` int(11) NOT NULL, `whouseid` int(11) NOT NULL, `qty` int(20) NOT NULL default '0', `remain_qty` int(11) NOT NULL default '0', `remain_cost` bigint(20) NOT NULL default '0', `cost` bigint(20) NOT NULL default '0', `userid` varchar(11) NOT NULL, PRIMARY KEY (`id`), KEY `goodsid` (`goodsid`), KEY `whouseid` (`whouseid`), KEY `fifotransferid` (`fifotransferid`), KEY `fifotransferid_2` (`fifotransferid`), KEY `transdate` (`transdate`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='transaction fifo' AUTO_INCREMENT=1 ;
the fifotransferid refers to this table but on different records. This will be signed with id. For example, if the goods was moved from one warehouse, this fifotransferid will refer to which record the goods was come from.
transtype -> 'invoice', 'ci', 'returngoods', 'purchasing', 'trx_in', 'trx_out', etc
transsigned -> 1 = in , 0 = out
bbmid = from goods purchasing so that i can track where this goods comes from.
is my database table was correct ?
i would like to see another table design for this Fifo Inventory control if you have one ...