POS Database Layout

Question

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.

Answer 1

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

Answer 2

By following the rules of data normalization, you would want to create a table like this:

store_id | sku    | quantity | other_attributes ... 
---------+--------+----------+---------------------
    1000 | 129832 |      234 |                  ...
    1000 | 129833 |      334 |                  ...
    1000 | 129834 |       23 |                  ...
    1001 | 129832 |        0 |                  ...
    1001 | 129833 |       12 |                  ...
    1001 | 129834 |       10 |                  ...
    ...

Essentially, it is a store_inventory table. That way, you can filter down to a given store by saying

WHERE store_id = 1000 

etc...

Answer 3

You really want three tables as far as I can see:

Product

ProductID
Price
Description
...

StoreProduct

ProductID
StoreID
Quantity
...

Store

StoreID
Address
...

That's a perfectly valid and normalised database design, and sounds basically what you have right now.

Answer 4

It sounds like it started on the right path with having one table for all of the common attributes of the item.

Instead of having an individual table for each store, the inventory counts for all stores should be in a single additional table, using the Item's Key and the Store's Key as a combined Primary Key on that table.

For Example

Items

ItemKey
Name
Price
SKU

Stores

StoreKey
Name
Address

Inventory

StoreKey
ItemKey
Quantity

Using the StoreKey and ItemKey together on the Inventory table will keep your records unique.

This will allow you to not only easily lookup the single item but also lookup such things as:
Quantity of all items at one store by the StoreKey
Quantity of one item at all stores by the ItemKey
What items are low at all stores by Quantity
etc