How to properly store leftover goods when there are n warehouses?

Question

MySQL

Hello!
Need help with proper storage of leftover goods when there are n warehouses.
Now there are 3 tables, storage of goods (≈ 300,000), storage of balances (≈ 1,000,000), warehouses (5):

CREATE TABLE `products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=224583 DEFAULT CHARSET=utf8;

CREATE TABLE `stocks` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `stock` int(11) DEFAULT NULL,
  `product` int(11) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `cid` (`product`)
) ENGINE=InnoDB AUTO_INCREMENT=536813420 DEFAULT CHARSET=utf8;

CREATE TABLE `stock` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(512) CHARACTER SET cp1251 NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

To get a list of goods and their balances, I made the following query:

SELECT products.id, products.name, GROUP_CONCAT(CONCAT_WS("|", stocks.stock, stocks.count) SEPARATOR ",") AS counts FROM products LEFT JOIN stocks ON products.id = stocks.product GROUP BY products.id LIMIT 0, 10

Query result:

After receiving data warehouses (counts) are divided by PHP function explode
Now questions:
1. Are there any better solutions to store leftovers?
2. How to filter correctly? (For example, if I want to only take into account real goods for two warehouses)
3. How can I get the balance without explode ?

Answer 1

Read about their Eloquent, write down the connections and then the work will be very simple.

$products = Products::with(['stocks', 'stocks.stock'])->find($id);
$product->name;
$product->stocks; // массив со стоками из stocks
$product->stocks[0]->name; // выведен данные из таблицы stock

Answer 2

In your tables socks.id doesn't seem to be needed at all, waste of memory and index.
This is similar to a cache (secondary) table, then you can generally get by with one table (or two), in each product you can store a list of warehouses with leftovers.
Save on indexes and it will work faster (without joins in the database).
If you need to filter by warehouse, then you can impose an index on the array of warehouses (if mysql already supports this).

Answer 3

isn't it easier to use inner join?
SELECT products.id, products.name, stock.name, stocks.count
from products
inner join stocks ON stocks.product = product.id
Inner join stocks ON stocks.stock =stock.id
GROUP BY products.id
further through where you can filter as you need.

Answer 4

You don't even need to store leftovers. It is easy to get the balances for any date and turnover for the period by full recalculation. Not a problem for modern iron