Clever Geek Handbook

Maintain inventory data in a separate table from product data

TL; DR: What are the reasons for keeping the "stock" data table separate from the product tables?

I created an app a while ago that stores a catalog of retail products. It includes standard attributes like size, color, image link, description, etc. Mostly flat tables. It is just indexed data of Magento products because the application is running on a separate server. He also had a column for quantity, which has no purpose; I just put it there, thinking "just in case for the future."

Now I need to implement some kind of inventory management in this application. I am researching how I should update / tweak the database structure and it seems that the systems prefer to have separate "stock" table (s) from the main product tables. The same is true for Magento. Why is this? (Note that my application does not need the ability to have separate stock levels for a given product.)

A few things that happened to me regarding this .. (basically the inventory will be its own object, apart from the product object)

  • Multiple stock pools for a given product.

  • Ability to track inventory changes (e.g. who / what is responsible for inventory changes, etc.)

  • Ability to sort stocks from different sources for reports or statistics.

  • Anything else?

Update:

Hazzit, who answered my question, pointed out the potentially very useful fact of caching MySQL tables if you have a lot of queries on a particular table. Read here HERE , but it is indicated that ..

If the table changes, all cached queries that use the table are invalidated and removed from the cache.

So, I would certainly benefit a lot from having a separate inventory table, since the main product table was not completely changed, but stocks.

Database Model Reference: http://www.databaseanswers.org/data_models/

+3


source to share


1 answer


• Anything else?

TL / DR: Yes, caching.

You've already listed most of the reasons why you might want a different table in terms of nomenclature, there are probably several other reasons for having a separate table (or even two). However, there is one more thing to consider: Inventory quantity changes much more often than most other product details. Depending on the database system, updating only one column may or may not have a significant performance limitation. For example: MySQL will invalidate all query caches whenever the underlying tables are updated. So if you are updating quantity_in_stock

, any query on that table will invalidate the cache - even a simple select name from products

one that doesn't even use a column quantity_in_stock

.



Real life example: Joomla has a column hits

in its article. Every time an article is viewed, it updates that column, with the result ... you guessed it! cleared request cache. Meaning: Whenever anyone accesses any article on the Joomla website, this poor database server will have to flush its query cache from what is usually the largest table in the entire database. You can very easily turn off request caching at this point.

Back to your question: if you don't expect your system to be under heavy load (like a public site), you only have to ask one question: will any product have more than one quantity in stock? Will any stock quantity ever relate to more than one product? If your answer is “No” in both cases, just put the column in the main product table.

+2


source






More articles:


All Articles