Data Structure advice

[John_Henry_Jose]

I am asked my Management to develop an Inventory Management which consists the following modules.

1. Master Inventory Items to be stored in different locations

2. Issuance - items are issued to Machinery, Client, Employees

3. Receipts - items are received back from Machinery, Client, Employees

Apart from this,

The Inventory items may be transferred to one storage location to another storage location

The Inventory items may be transferred to one country to another country with storage location

For all issuance / receipts / transfer - I need to the item condition also like good or damaged.

How the data structure supposed to be?

This is very challenging for me.

Please advice.

Thanks in advance.

@Aleksi @Steve @WillowMobileSys 

[dbaum]

Consider:

• Items table, with columns for static information about each item, such as ID, name, description
• Transactions table, with columns for information about each transfer, such as:
  • Item
  • Quantity
  • From location
  • To location
  • Type: issuance, receipt, transfer
  • Condition

[WillowMobileSys]

I agree with @dbaum 's approach.  There is one other important thing to track - current state of each item. 

Presumably, over time there will be multiple Transaction entries concerning each specific item.  You can capture the current state in many ways depending on your implementation:

1)   Additional columns in the Items table updated on each Transaction.

2)  Use the most recent Transaction to stipulate the current state - this may also require additional columns to specify other state details.

3)  Maybe it is best to also have a separate light-weight "Item Status" table.  For example, maybe there are apps in the system that do not need to see complete Item nor Transaction specific details ... only the current item status.    A light-weight table that combines a few details together would help keep these other apps small and efficient.

I hope this helps!

[John_Henry_Jose]

Do I need to include Country, Location in Master Inventory Table?

[WillowMobileSys]

These details would be part of that "item current state" I referred to.  They would be placed in whichever table you have decided to use to save those details. 

---

@John_Henry_Jose wrote:

Do I need to include Country, Location in Master Inventory Table?

---

Just to be perfectly clear, the "Master Inventory" table would be this "Item Status" table I was referring to.  I avoided using the word "Inventory" because it sounded like you were just looking to manage location of items and not quantities.  In retro-spect, it probably doesn't matter.  The location details should be part of the Inventory info.

You do not want to, in a larger system. combine Item details with Inventory tracking.  Instead the Inventory table would simply Reference the Item.  This separates management of the items list from the actual inventory tracking. 

Additionally, thinking on the item transactions, you could have more than one transaction per "current state" of an item.  AND you may want other types of transactions to be included in the inventory details such as Purchase Orders.

So, expanding on all of the above, I would suggest at least 3 tables:

• Items - as described above
• Transactions - as described above
• Inventory - a Ref to the Item and is updated by Transactions for "current state"

I hope this helps!

[John_Henry_Jose]

This gives me a clear idea what i am supposed to do. Almost closure.

But in transaction form, how to strict to enter like 3rd row's situation.

When I calculate the latest destination, it gives me like "Bahrain: MCSC", that is not correct also.

[WillowMobileSys]

---

@John_Henry_Jose wrote:

But in transaction form, how to strict to enter like 3rd row's situation.

When I calculate the latest destination, it gives me like "Bahrain: MCSC", that is not correct also.

---

I am not clear on what you are asking or what the issue actually is. 

However, it appears the sample sheet you provided is the Transaction table.  One thing I am noticing is that it doesn't appear to have a Ref to the Item table. It should. Maybe that is the source of the confusion?

[John_Henry_Jose]

Let me give you a complete picture. Kindly go through.

1. Item Table - Serial no. is the key

2. Transaction Table

I want to control the transaction date in such a way that system should not accept the pastdate for particular item or overlapped. for instance,

On 17th Jan - transferred "x" item to client site.

On 18th Jan - transferred "x" item to yard container.

In this case, system should not accept for 16th Jan to transfer "x" item to any another place, because, based on above 2 transaction, the "x" item was in "client site" and returned to yard on 18th.

So, I want to control the transaction date based on the Item.

I believe this gives you an idea.

Thanks in advance.

John