Inventory System Table Design

[szlamany]

This thread supplies information on a method for doing an INVENTORY system using a single table for stock-in and stock-out transactions. Using a single table allows for easy queries for stock-on-hand and other figures related to stock and date ranges. I am going to cobble posts from other threads and attempt to make an argument for this design.

[szlamany]

It does not make sense to me, in a SQL world, to have a RAW material table and a Order table.

You should have a single inventory table - something like this (and this is just for example purposes):

Product Id
Transaction Date
Transaction Type (C for CREDIT, D for DEBIT)
Transaction Sequence (if needed)
Reason Code (I for initial quantity, R for restock, O for order, A for adjustment)
Quantity

So you would end up with data like this:

ProdId   TransDate    Type  Seq  Reason  Quantity
000001  2004-02-01    C     001     I       50
000002  2004-02-01    C     001     I      100
000001  2004-02-10    D     001     O       20
000001  2004-02-10    C     002     R      100
000002  2004-02-20    D     001     O       99
000001  2004-03-01    C     001     A        2
000002  2004-03-01    D     001     A        1

Some initial stock of product 000001 and 000002 on 2004-02-01.

Then an Order and Restock of product 000001 on 2004-02-10.

Order of product 000002 on 2004-02-20.

Then some stock adjustments on 2004-03-01 - found out we had 2 more of product 000001 and that the remaining quantity of product 000002 was lost/damaged.

This main INVENTORY table drives everything about the quantity of stock - on-hand, ordered, sold - all figures - date driven.

You have additional tables for order info - linked however you want - maybe with product id, date and sequence - only when you have a REASON code of "O" for order.

This respects the "set-based" logic that you are supposed to use in SQL - and loses the old "record-by-record" processing techniques that we should avoid in SQL.

[RickyOswaldIOW]

Is this a question or an arguement? Just curious.

[szlamany]

Is this a question or an arguement? Just curious.

It is an issue that comes up every couple of months on the forum - being discussed this week in an old and resolved thread. I decided to create a new thread and if SimplyMe wants to continue the discussion we can have it here instead of hijacking that thread from 2 years ago...

Do you have any experience or opinions regarding inventory table design?

[Simply Me]

i already posted in that old thread but since you open up this one i would gladly continue our discussion here. The design is somewhat near to what i need.

I'll be posting here the last two post i had in that old thread.

Im not using bound controls. Im using ADODB to connect to my database. Here is the code in sub main i used to connect to my database

Public Sub openConnection()
  Set oConn = New ADODB.Connection
  
  With oConn
    .Provider = "Microsoft.Jet.Oledb.4.0"
    .CursorLocation = adUseClient
    .Mode = adModeReadWrite
    .ConnectionString = "Data Source=" & DBPath & "\" & DBName & ";Persist Security Info = False;Jet OLEDB:Database Password=xx;"
    .Open
  End With
End Sub

Regarding the separate tables of stock-in and stock-out. I have it so that i can have a record for stocks going out. If this is not a good idea then how should my tables look like and if I use only one tables for stock-in and out which field should I use to put relationship to my client table?

Sorry for my questions, I know this are basic ones which unfornately i still dont know yet and I'm very much willing to learn about it.

Thanks for your patience.

I just edited my database. please refer to the attached file.

[Simply Me]

Can we just use the fields i am using so that i'll not get lost in the processing of our discussion?

[szlamany]

Can we just use the fields i am using so that i'll not get lost in the processing of our discussion?

Your field names or my field names are not an issue.

Tell me what is the purpose of Quantity and QuantityOut in tblStockTransFile?

Why do you have two fields?

My example in Post #2 of this thread clearly shows that a single QUANTITY field is sufficient.

Granted I propose a CREDIT/DEBIT field to determine if it's a "in" or "out" of inventory - but it would be even simpler to remove the CREDIT/DEBIT field and simply have the QUANITY be a positive or negative number.

What is the purpose of your UNIT field??

[Simply Me]

The Quantity there is for stocks available while the quantityOut is number of stocks taken out or sold. its basically stock in and out.

The unit there is use to know if the item is on pieces, bar, tube, sachet, gallon, etc....

Could show me how your tables looks like as well as its relationships please?

[szlamany]

The Quantity there is for stocks available while the quantityOut is number of stocks taken out or sold. its basically stock in and out.

Get rid of QuantityOut - you do not need two fields. Put a negative number in the Quantity field if the stock is going out. Put a positive number in the Quantity field if the stock is coming in.

This allows: Select Sum(Quanity) From... to actually work - which is the whole point.

The unit there is use to know if the item is on pieces, bar, tube, sachet, gallon, etc....

If every time a product is referred to it's the same unit - like selling product XYZ is always done as BAR, for instance, then that field belongs in the tblStockMasterFile.

Could show me how your tables looks like as well as its relationships please?

Let's deal with the issues above first - we will build up your design if that's ok.

[Simply Me]

Get rid of QuantityOut - you do not need two fields. Put a negative number in the Quantity field if the stock is going out. Put a positive number in the Quantity field if the stock is coming in.

This allows: Select Sum(Quanity) From... to actually work - which is the whole point.

Ok for me.

If every time a product is referred to it's the same unit - like selling product XYZ is always done as BAR, for instance, then that field belongs in the tblStockMasterFile.

Yes product has the same unit.

Let's deal with the issues above first - we will build up your design if that's ok.

Can we build the design please?

[szlamany]

Ok - so if I understand your reply you are about to change your tables

tblStockMasterFile:
ProductId
ProductName
Unit - since this is the same for all transactions - it belongs here

tblStockTransFile:
ProductId
EntryDate - let's put this field up here - it's the second segment of the pri key
Quantity - positive for in-stock, negative for out-stock
ClientCode

Now for questions...

PurchasePrice - it can be argued that this belongs in some price schedule table - but let's deal with that later

ReorderPoint - seems to belong in tblStockMasterFile - this has nothing to do with an order being placed by a client - it's related to the product itself - just like the Unit - right?

DateOut is redundant - the ENTRYDATE field is the only field for date needed in this table.

In post #2 I discussed having a separate table for the ORDER to support the CLIENT associated with out-going-stock. You seem to want to have a single table for both inventory and order data - that's ok with me.

You might want to consider having a REASON code as I suggested in post #2 - so you can determine more easily the reason for a row being in the tblStockTransFile.

Please post back questions on any of these issues.

If you agree then please re-design your tables and post back a new image of the relationships.

[Simply Me]

Ok - so if I understand your reply you are about to change your tables

tblStockMasterFile:
ProductId
ProductName
Unit - since this is the same for all transactions - it belongs here

tblStockTransFile:
ProductId
EntryDate - let's put this field up here - it's the second segment of the pri key
Quantity - positive for in-stock, negative for out-stock
ClientCode

already done with it.

Now for questions...

PurchasePrice - it can be argued that this belongs in some price schedule table - but let's deal with that later

The purchaseprice of the same item may differ from time to time.

ReorderPoint - seems to belong in tblStockMasterFile - this has nothing to do with an order being placed by a client - it's related to the product itself - just like the Unit - right

DateOut is redundant - the ENTRYDATE field is the only field for date needed in this table.?

Ok.. done...

In post #2 I discussed having a separate table for the ORDER to support the CLIENT associated with out-going-stock. You seem to want to have a single table for both inventory and order data - that's ok with me.

You might want to consider having a REASON code as I suggested in post #2 - so you can determine more easily the reason for a row being in the tblStockTransFile.

fine with me.

Please post back questions on any of these issues.

If you agree then please re-design your tables and post back a new image of the relationships.

below is my new database.

[szlamany]

Ok - the design you now have matches in "concept" with what is suggested at the top of the thread.

Do you have any other questions? I'm leaving for a few hours...

[Simply Me]

How should i go about it inserting data to the tables, Do i need to use JOIN or a separate INSERT using begintrans and endtrans?


with the design we have can i generate the following reports later?

[szlamany]

Yes - you use separate INSERT's - BEGINTRAN and COMMIT or ROLLBACK is a nice clean method.

Those reports look very basic - and should easily be possible with a query and/or sub-query combination.

What is your backend database again??

[Simply Me]

Im using MS ACCESS but i really would like to use ms sql 2005 express but i dont know how much about and how to open a connection using it.

[szlamany]

Switch to MS SQL 2005 Express now - it's worth it.

Download the database and the free Management Studio Express software from MS.

Then visit www.connectionstrings.com to find out what's up with the connection string - it's really very, very easy.

You will be very happy that you switched - for many reasons - speed, better SQL syntax - more modern tools.

[Simply Me]

Ok. Hope you could help me through in learning it. Should i download MS SQL server 2005 Express or just the database and the free Management Studio Express software ?

[szlamany]

This download is the database - the server so to say (although it runs on a workstation)

http://www.microsoft.com/downloads/d...displaylang=en

and this is for Mgt Studio Express

http://www.microsoft.com/downloads/d...displaylang=en

[Simply Me]

Thanks! I'll be back after I downloaded and installed it.

[Simply Me]

I find your design in post #2 a very good one. I have some questions though.

1. what's the difference between the transaction type and Reason fields?
2. when do i use the two (i mean what scenario)?

With my current design, i am having problem on what to do whenever there is additional stock of a certain item. Since my productID is a PK then duplicate productID is not allowed...How do i solve such.

[Simply Me]

This is what i have far. I can enter records to my two tables already using the code below. Assuming I encoded the following products:
product ID: 00001
Product name: Tide Powder
Unit: Sachet
Purchase Price: 45
Quantity: 30
Reason: In
Entry Date: 9/11/07
Re-Ordere Point: 10


product ID: 00002
Product name: Colgate 150ml
Unit: Tube
Purchase Price: 70
Quantity: 25
Reason: In
Entry Date: 9/11/07
Re-Ordere Point: 15

My problem here is since my productID fields for both tblStockMasterFile and tblStockTransfile are PK, then two does not allow duplicate entry, right? so how about if on 9/12/07 i have additional item to be entered for product 00001 how should i be able to add the item without getting the sum of the item just entered to the previous item?

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)

    'check whether user has change the PK otherwise it'll not be saved
    If Checker = True Then
      Call msgExist("Product")
      txtProdID.SetFocus
      ' Start highlight before first character.
      txtProdID.SelStart = 0
      ' Highlight to end of text.
      txtProdID.SelLength = Len(txtProdID.Text)
      Exit Sub
    End If  'Checker

On Error GoTo ErrHandler
  oConn.BeginTrans

      sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
              "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
    oConn.Execute sSQL
  
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
                 '  Debug.Print sSQL
    oConn.Execute sSQL

  oConn.CommitTrans

  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")

  txtProdID.SetFocus
  Exit Sub

ErrHandler:
  Call msgError(Err)
End Sub 'cmdSave_Click

I am attaching the data entry form i created in vb. I dont know if this is the best way to do it or do i need to have separate data entry form for the tblStockMasterFile and tblStockTransFile?

[szlamany]

I find your design in post #2 a very good one. I have some questions though.

1. what's the difference between the transaction type and Reason fields?
2. when do i use the two (i mean what scenario)?

With my current design, i am having problem on what to do whenever there is additional stock of a certain item. Since my productID is a PK then duplicate productID is not allowed...How do i solve such.

Transaction type of C is for a credit - stock coming in - D is for a debit - stock going out. That field is used for building the SUM() formula

Select ProdId,Sum(Case When Type='C' Then Quantity Else -Quantity End) From...Group by ProdId

That SELECT give you total stock on hand - using the TYPE field to determine a positive or negative number. You can do away with the TYPE field if you put the "-" into the value itself in the QUANTITY field for debit amounts.

The PK of this table should be: (ProdId,TransDate,Type,Seq) - with those 4 fields duplicates are not possible (the Seq value forces this).

Does this answer your question in post #22 also??

[Simply Me]

In my mind, I used the Reason fields for Stock In (In/C) and Stock Out (Out/D). Is it good enough?

Does this answer your question in post #22 also??

As I've mentioned, I can stockIn a product using the code I used in cmdSave--there's no problem with is, but when I tried adding the same product (additional stock-In) I can't add anymore because the PK won't allow me. How do I resolve that?

[szlamany]

If you feel REASON can indicate C or D for stock in or out - then that's fine.

I would not do that - but I've got different experiences that put me in that position. I cannot bring you there.

For your second question - what fields are in your PK? The sequence # should the last segment in the compound PK and it should be incremented to allow for unique PK's.

[Simply Me]

If you feel REASON can indicate C or D for stock in or out - then that's fine.

I would not do that - but I've got different experiences that put me in that position. I cannot bring you there.

For your second question - what fields are in your PK? The sequence # should the last segment in the compound PK and it should be incremented to allow for unique PK's.

tblStockMasterFile:
ProductID (PK)
ProductName
Unit
ReOrderPoint

tblStockTransFile:
ProductID (PK)
EntryDate (PK)
PurchasePrice
Quantity
Reason
Client Code

tblClientMasterFile:
ClientCode (PK)
ClientName
Budget

here's my code:

Private Sub cmdSave_Click()
  If CheckNullValue = False Then Exit Sub
  Call ReplaceQuotation(txtProdName)


  If Checker = True Then
      Call msgExist("Product")
      txtProdID.SetFocus
      ' Start highlight before first character.
      txtProdID.SelStart = 0
      ' Highlight to end of text.
      txtProdID.SelLength = Len(txtProdID.Text)
      Exit Sub
    End If  'Checker

On Error GoTo ErrHandler
  oConn.BeginTrans

      sSQL = "INSERT INTO tblStockMasterFile(ProductID, ProductName,Unit,ReOrderPoint)" & _
              "VALUES( '" & txtProdID.Text & "','" & txtProdName.Text & "','" & txtProdUnit.Text & " ', '" & txtProdReOrderPoint.Text & "')"
    oConn.Execute sSQL
  
      sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
                 '  Debug.Print sSQL
    oConn.Execute sSQL

  oConn.CommitTrans

  Call FillListView(lstStockMasterFile, rsStock)
  Call ClearFunction(frmStockMasterFile, "TextBox")

  txtProdID.SetFocus
  Exit Sub

ErrHandler:
  Call msgError(Err)
End Sub 'cmdSave_Click

'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
  Checker = False

  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
         "FROM tblStockMasterFile " & _
         "WHERE ProductID = '" & (txtProdID.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly

  If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function  'Function Checker

Private Sub Form_Load()
   Call openConnection
  Me.Left = LeftPos - 200
  Me.Top = TopPos - 200

  Set rsStock = New ADODB.Recordset
  Set rsTempStock = New ADODB.Recordset
  rsStock.CursorLocation = adUseClient

'  sSQL = "SELECT ProductID, ProductName,Unit, ReOrderPoint FROM tblstockMasterFile"

  sSQL = "SELECT tblStockMasterFile.ProductID, tblStockMasterFile.ProductName, " & _
          "tblStockMasterFile.Unit, tblStockMasterFile.ReOrderPoint, " & _
          "tblStockTransFile.EntryDate, tblStockTransFile.PurchasePrice, " & _
          "tblStockTransFile.Quantity, tblStockTransFile.Reason " & _
              "FROM tblStockMasterFile INNER JOIN tblStockTransFile ON " & _
                  "tblStockMasterFile.ProductID = tblStockTransFile.ProductID"

  If rsStock.State = adStateOpen Then rsStock.Close
  rsStock.Open sSQL, oConn, adOpenStatic, adLockOptimistic

  Call FillListView(lstStockMasterFile, rsStock)
End Sub 'Form_Load

[szlamany]

Post #2 and post #23 both show a field called Seq - which is part of the primary key.

You need to have that field in tblStockTransFile - and have it be a segment in the PK.

That field needs to have 1, 2 or 3 (and so on) in it to make the PK unique.

[Simply Me]

You need to have that field in tblStockTransFile - and have it be a segment in the PK.

That field needs to have 1, 2 or 3 (and so on) in it to make the PK unique.

Im sorry, I can't get what you mean here.

[wes4dbt]

Perhaps i'm offbase, if so just ignore this post,

If your planning to due any detailed reporting on Sales and Purchase history there seem to be important things that are missing.

Some type of order number (Sales Order# or Invoice# or Purchase Order#), something to track this transaction. Can't use client ID and date because they could have more than one transaction per day.

There are some quantities that could be helpful to your sales people
committed - sold but not shipped
on hand - Actual quantity in physical inventory
availiable = on hand - committed
on order - Ordered but not in inventory

Maybe this is more than you need, just wanted to mention it while you were in the design stage of your project.

[szlamany]

@wes4dbt - from post #2

You have additional tables for order info - linked however you want - maybe with product id, date and sequence - only when you have a REASON code of "O" for order.

I'm trying so hard to get the point across that the basic inventory count needs to be stored as inventory detail rows in a transaction table.

I fully realize that order info needs to be in a different table - as stated in post #2 (and quoted above).

Right now I'm trying to get Simply Me to understand that a "Sequence Number" field added to the table - and made part of the compound primary key - can offer a unique PK when more then one entry is done for a single date.

[wes4dbt]

I admire how much time you have spent helping Simply Me.

Even a simple inventory system need a Product Master table and a Product Detail table. Otherwise just create an EXCEL spreadsheet.

The most important things are setting the relationships and the primary keys. After that your main concern is what information do I want to track and in how much detail.

I think sooner or later the programmer will be sorry if they don't use a relational DB.
Might as well design with the future in mind.

For what it's worth.

Good luck

[Simply Me]

Right now I'm trying to get Simply Me to understand that a "Sequence Number" field added to the table - and made part of the compound primary key - can offer a unique PK when more then one entry is done for a single date.

I guess Im already aware of it that I need to add the Sequence number field. My question is-- is this something that the user will input or it should be an autonumber field?

Kindly please take a look my code. I know that the function below does not let me have more than one entry of the same product. when I removed the code i am still not able to add products since my productID needs unique value.

Kindly please take a look at my code above. What should it look like?

'TODO : return true if ProductID already exist
Private Function Checker() As Boolean
  Checker = False

  If rsTempStock.State = adStateOpen Then rsTempStock.Close
  sSQL1 = "SELECT COUNT(ProductID) as ProductIDExist " & _
         "FROM tblStockMasterFile " & _
         "WHERE ProductID = '" & (txtProdID.Text) & "'"
  'Debug.Print sSQL1
  rsTempStock.Open sSQL1, oConn, adOpenKeyset, adLockReadOnly

  If rsTempStock("ProductIDExist") > 0 Then Checker = True
End Function  'Function Checker

[Simply Me]

I admire how much time you have spent helping Simply Me.

Even a simple inventory system need a Product Master table and a Product Detail table. Otherwise just create an EXCEL spreadsheet.

The most important things are setting the relationships and the primary keys. After that your main concern is what information do I want to track and in how much detail.

I think sooner or later the programmer will be sorry if they don't use a relational DB.
Might as well design with the future in mind.

For what it's worth.

Good luck

I'm very much thankful that szlamany had been so patient in sparing his time for me.

If you try to look at the things i've posted I think i have the Product Master table and a Product Detail table already.

[wes4dbt]

If you decide to use a sequence number, it will be used in only the Detail table. You don't need it in the Master table because there is only one record per product. In the detail table the PK could be ProductID+SequenceNo, this would allow you to track all the various transactions.

In my opion the system should generate the sequence# and the operator should not be able to edit it. But because there is always a chance of data corruption or maybe the company just wants to start each new year with a fresh seq#, there should be a some way to reset the current seq# and this can be done thru a utility function. I hardly ever use an autonumber field because of the lack of flexability, I like to control the number with code. It's more work but worth it, but thats a design decision you must make.

You don't have to use a seq# there may be other options, it depends on what is going to be entered for each detail record.
example - ProductID+OrderNo ' where OrderNo is always unique

Using a seq# is a good method, especially if you not 100% sure of whats going to be entered.

[Simply Me]

Ok this is my scenario:

1. The user will enter the names of clients as well as their monthly budget
2. The user will enter(stock In) the product Id, product name, purchase price, unit, quantity, Date of entry, and the re-order point.
3. One product can have different purchase price, so table should contain the product but with different purchase price. (this where I dont know what code should use since the product ID is a PK)
4. The user will enter(Stock Out) the product the client will order.

[wes4dbt]

Store the purchase price in the detail record not the master record.
or
If you absolutely need to have a master record for a product with more than one price then entry the product multiple times with different ProductID's.

Are you sure you need multiple prices in master? Why

[Simply Me]

Certainly one product will have more than one price since prices in the market is not fixed. What you bought today for 100 may become 110 tomorrow. So if this is the case therefore the selling price will also be different. the mark-up price is 15% of the purchase price.

[szlamany]

Im sorry, I can't get what you mean here.

Do you understand the SEQ field and how it makes the PK unique?

3. One product can have different purchase price, so table should contain the product but with different purchase price. (this where I dont know what code should use since the product ID is a PK)

It seems by this statement that maybe you don't.

This should be the tblStockTransFile:

tblStockTransFile:
ProductID (PK)
EntryDate (PK)
SeqNo (PK) - this field gets added to the PK
PurchasePrice
Quantity
Reason
Client Code

That SEQNO field gets the value 1 for the first entry for a product on a given entry date. So almost always this field is simply a 1. In the situation where a second row is added for a product on the same ENTRYDATE then you make this value a 2. A 3 for the third an so on.

This is such a simple concept - we use it all the time for NOTE tables where someone enters 1 or 2 lines of text on a given date.

We find it extremely easy for the client program to manage this SEQNO field - either show it to the user or don't - doesn't really matter. We usually show it to the user and the GUI forces the column value to be unique for a given date.

You could even do the INSERT with a 1 and if it fails change it to a 2 and try again - that's a reason method in SQL to get a unique value also.

[Simply Me]

Yes, you're right i really dont understand SEQ and thanks for the explanation. I already added SEQ to my table. If you look at the code in post #26. What am i missing there? so that what you mentioned above will work out?

[szlamany]

The INSERT in post #26

sSQL = "INSERT INTO tblStockTransFile (ProductID, EntryDate, PurchasePrice, Quantity, Reason)" & _
              "VALUES('" & txtProdID.Text & "','" & txtEntryDate.Text & "','" & txtProdPurchasePrice.Text & _
                   "','" & txtProdQTY.Text & " ','" & txtReason.Text & "')"
                 '  Debug.Print sSQL

I don't see a SEQNO in that list of fields being inserted.