0

I currently have 3 tables: locations, products, and stock, which are pretty self-explanatory:

The fields of stock are location_id, product_id, and quantity, which is great... until I add a new location (or product, but less so, since the number of locations is normally quite small).

In production, we have 20k+ products, which means that if a new location is created, 200k+ records must be created in stock, which seems quite ridiculous to me. I'm sure it's not that bad in reality, but it still feels like something that should be avoided.

So, is there any better way to do this, or is this the normal best practice?

Improve this question
3
  • 2
    Well firstly there's really no tangible number of rows that matter as far database performance goes, and 200k isn't that many rows. But I don't follow your math or the why of it too. What does adding a new location have to do with how many products you have?...unless you're storing every possible product and location combination in the stock table, with 0 quantity for whenever you add a new location. If so, that's silly and probably not necessary. But based on the schema you've provided, as far as I'm concerned, adding a new location should only result in 1 new row.
    – J.D.
    Jan 12 at 14:38
  • 1
    @J.D. Ohhh, I suppose I don't need to make an entry when the quantity is 0 - you're right; that made no sense "^^
    – Basil
    Jan 12 at 14:45
  • 1
    INNER JOIN returns no rows for the result when any one of the tables is missing the desired row. LEFT JOIN will return NULL, which can be turned into 0. So, even if you need 0, you don't necessarily need "rows with 0s". Let's see the SELECT if you want to discuss further.
    – Rick James
    Jan 12 at 20:51

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.