Need your suggestion in designing my database

[siamandm]

Hello All,
I want to create a database for my store to track the inventory, i have create three tables as below:

Screenshot 2024-04-17 162409.png

using this design is overwriting the quantity on hand in table product, so i decided to add another table to keep the original qty on hand
so i decided to add this table to my design as below this way i can keep purchased and sold item numbers:

Screenshot 2024-04-17 162654.png

now how to connect this table to other tables, or is their a better design ?

Regards

[HansV]

How do you want to use the TransactionT table?

[HansV]

You may find Ken Sheridan's example of an inventory database useful. You can download Inventory.zip from Ken Sheridan's public database examples.

And a series of tutorials: How to create a stock control database.

[Gasman]

You would normally calculate the QtyOnHand, not store it.

[siamandm]

Thank you very much for replies
based on your suggestion i came up with this design, but I'm not sure about its bugs and problems if you could you help me on this please
I have two junction tables, one OrderDetailT and another PurchaseDetailT

Screenshot 2024-04-18 152414.png

[HansV]

In the PurchaseDetailT table, you do not need a SupplierID field - the supplier is implicated by the PurchaseID field.
The table should have a ProductID field, and the link to ProductT should be from that field instead if from PurchaseID.

[siamandm]

Thank you very much for the reply, now the design became like this, does this design allow me to track the quantity on hand without overwriting the original quantity purchased and entered the stock?

Screenshot 2024-04-21 083741.png

[HansV]

That looks fine. In this version, the stock on hand is not stored in any table. It can be calculated on the fly in a query as the sum of all Quantity values in PurchaseDetailT where the related PurchaseDate in PurchaseT is less than or equal to the current date, minus the sum of all Quantity values in OrderDetailT where the related OrderDate in OrderT is less than or equal to the current date

[siamandm]

This sounds good, so we can calculate it at any time on the fly :)

Thanks again for your continues support.

regards