Store balances and prices in a separate table?

Question

Database design

A question. Where do you think is the best place to store leftovers? In the product table, in which there are dozens of fields or separately? the same goes for prices.

The fact is that there are more than 100,000 products. Every 10 minutes, a worker is launched that updates the balances and prices with conversion to exchange rates. And as a consequence, the attribute "Presence" of the goods Yes or no. And it turns out you have to update these fields in the tower. The product has too many versions, which is expensive to store in the database.

Now I started storing prices in a separate linked table and only update it. Now I also plan to put the rest into a separate table, and the available sign into a separate one too. What do you think would be better from a design point of view?

Answer 1

In general, good form from the point of view of design is the absence of dubbing. In this case, the derivative from the master data (the balance as the difference between receipts and expenditures) is a double.
This is worth going for only for the sake of a real increase in productivity.
The actual calculation of the free balance is a fast thing. For example, even the calculated field "is there a free stock" with a range of 1.5 million positions and an average of 3-4 levels of replacement / analogue trees is "not noticeable at all" in performance. (MS SQL 4-8 cpu/vcpu 8-16-24 Gb RAM).
With prices - a little more complicated - there can be many pricing schemes. Therefore, it is better to store the "mathematics of schemes" and already from this calculate the conditionally final price. And for the needs of accounting, operational, management accounting - to calculate in accordance with those needs.

Answer 2

SKU