Need suggestion on Inventory System database design

[reevenz]

Hi All,

I am going to write a small inventory tracking app for a small company.

What they need is only to keep track goods in, goods out, supplier, customer, and purchase price and sell price so we are able to calculate purchase price, sales price, and stock value.

1. Puchase table is for goods in, it links to product table, have timestamp, quantity and price attributes to calculate the purchased price
2. Sales table is for goods out, it links to product table, have timestamp, quantity and price attributes to calculate the sale price

My question is:
When we need to calculate the number of stock on hands, and the stock value, do we need to calculate the purchase table minus sales table? Also for calculating the values using First In First Out method? Normally this would be the easiest way, but I am thinking in the future where we are having large number of record, this method will be very expensive in term of processing power.

What would be the alternative for this?

I'll be appreciate all kind of input.

Thanks in advance.

[dotyell]

Though this ain't db design question I will help u out. When u make a transaction, subtract the transacted stock from the product. When doin' a stock take, simply, the product table will have the stock on hand.

[bigakis]

Some notes:
1)Keeping the stock levels at the product table will limit you forever to only one warehouse. It is very simple to
plan for more than one warehouses but will be very difficult to do it after.
2)Use only one table for journals. You can separate sale entries from purchase entries by a "Journal kind" code.
This way you can have more than one purchase journals (GRN, Purchase Invoice, Purchase Returns, Vendor Discounts,...)
and the same applies for sales.
3)Your friend ask you for a simple program today. I bet that he/she will ask for more features very very soon.
Plan for these features as they were asked.

A very simple and versatile schema follows. You can adapt it to your needs by adding columns or changing types.

--Warehouse table: distinct places where to keep stock levels.
--It is almost sure that stock will be kept and monitored in mmore than one place.
create table warehouse
(
  war_code number(3) not null,
  war_name varchar2(100),
  primary key(war_code)
);

--Journal kind:Describes what each journal is doing and allows users to post different kind of journals
--This is the most important table as it holds the "heart" of the calculations.
create table journalkind
(
  jki_code number(3) not null,
  jki_name varchar2(100) not null,
  jki_module varchar2(1) not null, --(S)ales, (P)urchases, (I)nventory adjustments, .......
  jki_qty  number(1) not null, --Effect on the stock (0)=No effect, (1)=Add stock (-1)=Removes stock
  jki_val  number(1) not null --the same for values
)

--Journal header.
create table journalhd
(
  jhd_number number(10) not null,
  jhd_kind   number(3) not null references journalkind(jki_code),
  jhd_date   date not null,
  jhd_user   varchar2(30), --the user created the journal.
  jhd_wareh  number(3) not null references warehouse(war_code),
  jhd_posted number(1) not null default 0, --initialy has value 0. After the journal is complete is changed to 
                                  --value 1 possibly by pressing a button "SAVE" or POST.
                                  --A good practice is not allowing any change to posted journals.
  jhd_canceled number(1) not null default 0 --Normally 0 but set to 1 if the journal is cancelled. 
                         --Another option is to delete the journal but a record may be required
);
--Journal Lines.
create table journalline
(
  jli_number number(10) not null references journalhd(jhd_number),
  jli_lineno number(6) not null,
  jli_itemno number(10) not null references <item table>, --Item code.
  jli_qty    number(10,2), --quantity
  jli_uprice number(10,2), --Unit price
  jli_vatper number(10,3), --vat percent
  jli_discpec number(10,3), --discount percent
  jli_discval number(10,2), --discount value
  jli_vatvat  number(10,2), --vat vatlue
  jli_lineval number(10,2), --total line value
  primary key(jli_number, jli_lineno)
);

The stock can be kept in a separate table that will be updated by a database trigger or calculated 
on-line through the following view:
create or replace view stock as
select jli_itemno                 as itemno,
       jhd_wareh                  as wareh,
       sum(jli_qty) * jki_qty     as stock_quantity,
       sum(jli_lineval) * jki_val as stock_value
  from journalkind,
       journalhd,
       journalline
 where jki_code     = jhd_kind
   and jhd_posted   = 1
   and jhd_canceled = 0
   and jli_number   = jhd_number
  group by jli_itemno, jhd_wareh;

I can give you a more detailed schema but it may confuse you.
Good luck

[Doomer D. Great]

bigakis, your schema is very good. However, I have a need for a more complex and practical schema.

I'm looking to handle the following:

1 - Handling the changing prices of products with the passage of time. Storing a price in a product table is death!

2 - Some products are sold as unit. But some items are sold as Dozen or Pack of 12 or 24 etc. Manage a Units table

3 - Location of a product in an inventory

4 - Information of Shelves, Racks, Cabinets, and warehouses etc has to be stored

There are many more but these are all I can remember ATM.

[asaukani]

Hi All,

I am going to write a small inventory tracking app for a small company.

What they need is only to keep track goods in, goods out, supplier, customer, and purchase price and sell price so we are able to calculate purchase price, sales price, and stock value.

1. Puchase table is for goods in, it links to product table, have timestamp, quantity and price attributes to calculate the purchased price
2. Sales table is for goods out, it links to product table, have timestamp, quantity and price attributes to calculate the sale price

My question is:
When we need to calculate the number of stock on hands, and the stock value, do we need to calculate the purchase table minus sales table? Also for calculating the values using First In First Out method? Normally this would be the easiest way, but I am thinking in the future where we are having large number of record, this method will be very expensive in term of processing power.

What would be the alternative for this?

I'll be appreciate all kind of input.

Thanks in advance.

what we usually do is to put attribute stock on hand, stock on order, min stock and reorder point in the master item
So each time the item is purchased, we add stock on order
when the goods is received we add stock on hand and deduct stock on order
when it is sold, we deduct stock on hand
by this way we know stock for each item just by check master item without going to purchase table or sales table

[bigakis]

Lets start by pricing. It is always a good practice to have multiple pricelists to be free to choose the pricing scheme for each individual customer or POS. So we create the pricelist table:

/*Price list definition
 *pr1_kind: S if this pricelist is used in Wholesale Sales 
            R is this pricelist is used in Retail Sales
            P if refers to products purchased
            C if it refers to product Cost. Cost prices are updated
              by a costing procedure. Not in the scope of this thread.
*/
create table pricelist1
(
  pr1_code      number(10) not null
  pr1_name      varchar2(30) not null,
  pr1_kind      varchar2(1) not null, 
  pr1_currency  varchar2(3) not null,
  primary key(pr1_code)
);

On each pricelist we should have the prices per item. In order to have price history we add a date field that keeps the date the price starts to be effective. A new record for the same product at another date will change the price. For example product 1 has the price of 10$ up to 13/6/2010. After that date the price should be 12$ (nothing is getting cheaper these days...). To handle this case we should add 2 records:
1/1/2000, product 1, 10$ (a very old date)
13/6/2010, product 1, 12$
Now the prices table:

/* Pricelist Prices
 * pr2_date: The date the price is effective.
 */
create table pricelist2
(
  pr2_code      number(10) not null references pricelist1(pr1_code),
  pr2_date      date       not null
  pr2_itemno    number(10) not null,
  pr2_price     number(10,2) not null,
  primary key(pr2_code, pr2_date, pr2_itemno)
);

Because there are many prices per product, when looking for THE price, we should first know the pricelist.
This can be found in many ways depending on our needs. As an example when selling to a customer we can define
at the customer table the preferred pricelist to use:

create table customer
(
  cus_code number(10) not null,
  ......
  cus_prlist number(10) not null references pricelist1(pr1_code)
  .....
);

If on the other hand we have a retail store we can define the preferred pricelist at the store level.

In order to simplify code, we will create a PL/SQL (I only use Oracle....) package to handle prices:

create or replace package prices_pkg as

function GetPrice(pPrList in pricelist1.pr1_code%type, 
                  pDate   in date,
                  pItemNo in pricelist2.pr2_itemno%type
                 ) return pricelist2.pr2_price%type;

end;

create or replace package body prices_pkg as

function GetPrice(pPrList in pricelist1.pr1_code%type, 
                  pDate   in date,
                  pItemNo in pricelist2.pr2_itemno%type
                 ) return pricelist2.pr2_price%type is
  cursor c1 is
  select pr2_price
    from pricelist2, pricelist1
   where pr2_code   = pPrList
     and pr2_date  <= pDate
     and pr2_itemno = pItemNo
     and pr1_code   = pr2_code
   order by pr2_date desc;
  f1 c1%rowtype;
begin
  --Fetch only one value. This will fetch the last record before pDate date.
  open  c1;
  fetch c1 into f1;
  close c1;
  return f1.pr2_price;
end;

end;

The SQL to get the current prices for some products will be:

select prd_itemno, prd_descr, pricelist_pkg.GetPrice(1, sysdate, prd_itemno) as price
  from products
 where .....

So simple. Another bonus of this scheme:We can anytime change the GetPrice function to handle more complex situations. For example if we need the price always in local currency, we can add a Currency->LCY transforamtion in the function without changing a single line of code in the application.

I will answer the other parts in another post.

[anilgopinath]

Hi bigakis

Can I get your schema for the Inventory System database design.

[Shuvadeep]

@bigakis could you enlighten how recurring invoice can be included with this schema!!