CODEWITHSUNDEEP
sqlarchitecture
Roger
Roger

Reputation: 597

stock table for inventory database design architecture

I am currently building a management system. Is it good practice to create a balance table for inventory to store the inventory at hand and constantly update the table if there are changes, or should one just directly query total inventory ordered table - total inventory used table? Which would be the most efficient and fastest way to do?

Upvotes: 0

Views: 1146

Answers (1)

the4thamigo_uk
the4thamigo_uk

Reputation: 875

It is likely a bad idea to use two separate tables. You will have to perform a join which is unnecessary. Simply have one table with an 'ordered' column and a 'used' column. In your query you can very efficiently calculate the net value e.g. :

SELECT ordered, used, (ordered - used) as net FROM inventory

Upvotes: 1

Related Questions