Inventory System Table Design

[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.