CODEWITHSUNDEEP
sqldatabasedatabase-design
da1lbi3
da1lbi3

Reputation: 4519

Database inventory system design SQL

I am rebuilding a current inventory application. And I am looking for improvements.

The current situation: I have a product that can be stored on several locations (1 or many). Even, 1 location can hold multiple (different) products.

So I have this design:

product:
 - id
 - description

location
 - id
 - name

stock:
 -id
 -product_id
 -location_id
 -amount

That is what I want. But now the main issue. Calculating the total stock per product takes to long. I have around 20K products and 18K locations. So I thought I can save the total stock at the product table. Is this a bad approach?

Also, I want to save where stock is coming from and to which location it is shipped. So I want to make 2 tables for it. One for incoming and one for outgoing stock. Is there a better solution for this?

Upvotes: 0

Views: 675

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269553

So I thought I can save the total stock at the product table. Is this a bad approach?

Yes, this is generally a bad approach. Instead, you should just calculate the value for a given product:

select sum(s.amount)
from stock s
where s.product_id = @product_id;

With an index on stock(product_id, amount), this should have very good performance.

Why is duplicating the information at the product level bad? The problem is maintaining the data. You would have to have triggers for inserts, updates, and deletes on the stock table. This is cumbersome to maintain.

Under some circumstances, such an approach might be necessary. But with only 20,000 rows, you should be able to optimize the database without resorting to triggers.

Upvotes: 3

Related Questions