Our current database layout for store inventory includes a table that houses a record for each item and includes the item attributes such as price, cost, description, SKU, etc. Each store then has a table that has the SKU and the Quantity, and a few other attributes that are different for each store depending on the SKU.
Although this is better than having a column for each store's quantity in the Items table, it seems that this too is a somewhat ugly solution as when a new store is added, you need to select into a new table and when a new item is added, you have to insert into not only the items table but each individual store table.
Is there a better way to go about this than how I'm seeing it? It feels like there is.
Why not have a stock table with SKU, StoreId and Stock levels?
e.g.
Sku | StoreId | StockLevel
1234 | 001 | 15
1235 | 001 | 16
1234 | 002 | 8
1235 | 002 | 0
so, store 002 is out of stock of sku 1235, but store 001 has plenty. This table layout also allows you to get a group-wide view of your stock levels in a single table using something like
select sku, sum(StockLevel) from stock group by sku