Inventory Database Design Issue

Question

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?

Answer 1

I agree with TomTom on the merits of the Datamodel resource book - it will give you more insight than StackOverflow, and if memory serves, there's a fairly detailed discussion of exactly this scenario.

The design you've come up with is similar to what I've seen for other systems - what you call ProductVariation is often called "variant".

I'm a little confused about the role of SKU in your design - the acronym stands for "Stock Keeping Unit". As it's supposed to be unique and never change, I'd make that the primary key of your ProductVariation table, rather than ID.

If you're only ever going to sell your current product range, or are happy to refactor in future, you can leave it as is; alternatively, you could consider a design with "table per subclass". This would give you a schema along the lines of:

• Product (id, name, categoryID, description)
  • SizeVariant (SKU, productID, finish, width, thickness, Size, location, quantity)
  • ColourVariant (SKU, productID, Colour, finish, location, quantity)
  • NoVariant (SKU, productID, location, quantity)

Note how I've also put some of the attributes you've currently assigned to "Product" into the child tables.

This keeps the schema more "self describing", but adds a lot of work - only do that if you think the product catalogue will grow in the future, and you want to avoid EAV.

Answer 2

What you have looks pretty good to me. The only modification I would suggest at this stage is that instead of the size ID being blank on ProductVariation records that only have a single Variant per Product, it should instead link to a "Standard Size" or "Size not applicable" record, for reasons of referential integrity and not null constraints.

Answer 3

THE DATA MODEL RESSOURCE BOOK, Volume 1.

http://www.amazon.de/Data-Model-Resource-Book-Enterprises/dp/0471380237/ref=pd_bxgy_eb_text_b

Get it, read it ;)

There are two other volumes for more detailed stuff.