I own a jewelry retail store alongside an online store, and am in the midst of building a completely custom system to track all of my inventory. I also have a number of stockists (other stores) that sell my items on consignment, so I want to keep track of stock levels there too.
I primarily sell rings, and have thousands of them in storage. I keep one of each size/style in stock at my physical store. They come in a variety of sizes and styles. I want to avoid the EAV approach completely and am happy to have sparse fields, there aren't many attributes.
The main product table, I included a number of attributes in here because they don't vary for each product. Size varies, so I have addressed that using other tables.
Product Table
- id
- name
- categoryID
- description
- finish (polished, brushed, satin etc)
- width
- thickness
Each ring comes in 6 sizes, so rather than creating 6 duplicate entries in the product table I created the productVariation table which references the productID. At the moment this is only used for storing the different sizes. Each different sized ring will have a unique SKU and barcode, so I have put that in here rather than the main product table.
ProductVariation Table
- id
- productID
- sizeID
- sku
Then I have to keep quantity levels for several locations, so I made the productQuantity table to hold that information.
ProductQuantity Table
- id
- productVariationID
- locationID
- quantity
Am I on the right track? One thing I haven't figured out yet is how to add a product that has no variations, for example a watch that only comes in one style and has no size options. It would have to be added to the productVariation table with a blank sizeID since that's where the SKU is stored. Any ideas to improve this design?