TL;DR: what are some reasons to keep "stock" data table separate from products tables?
I've built an application a while back that stores a catalog of retail products. It includes standard attributes such as size, color, image link, description, etc. in mostly flat tables. It's just an indexed data of Magento products because the application runs on a separate server. It also had a column for quantity, which doesn't have any purpose; I just put it there thinking "just in case for the future."
Now, I need to implement some kind of inventory management on this application. I've been researching how I should update/set up the database structure, and it seems that systems prefer to have a separate "stock" table(s) from the main product tables. This is true for Magento as well. Why is that? (Note that my application doesn't need the ability to have separate stock levels for a given product.)
A couple of things that occurred to me regarding this.. (basically, inventory will be its own object apart from the product object)
Multiple stock pools for a given product.
Ability to keep track of stock changes (e.g. who/what responsible for altering stock, etc.)
Ability to segregate stocks from different sources for reports or statistics.
Anything else?
Update:
Hazzit, who responded to my question, pointed to a potentially very useful fact of MySQL table caching, if you have a lot of queries against a particular table. Read about here HERE, but it is pointed out that..
If a table changes, all cached queries that use the table become invalid and are removed from the cache.
So, I would certainly benefit a lot from having a separate inventory table, as the main product table isn't altered a whole lot, but the stocks are.
DB Model Reference: http://www.databaseanswers.org/data_models/
•Anything else?
TL/DR: Yes, caching.
You've already listed most reasons why you may need another table from a nomalization point of view, there probably are a few more similar reasons to have a separate table (or even two). There is something else to consider, though: Quantity in stock gets altered a lot more often than most other product information. Depending on the database system updating just one column may or may not come with a major performance penalty. E.g.: MySQL invalidates all query caches upon any update on the underlying tables. So if you're updating quantity_in_stock, any query on that table will get its cache invalidated - even a simple select name from products which doesn't even use the quantity_in_stock column.
Real life example: Joomla has a hits column in its article table. Every time an article is viewed, it updates that column resulting in... you guessed it! a purged query cache. Meaning: Whenever anybody accesses any article on a Joomla website, that poor database server will have to clear its query cache on what usually is the largest table in the whole database. You may very well just deactivate query caching at that point.
Back to your question: Unless you're expecting your system to be under heavy load (e.g. public facing website) you should only as yourself one question: Will any product ever have more than one quantity in stock? Will any quantity in stock ever be relevant for more than one product? If your answer is "No" in both cases, just put the column into the main product table.