Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

G'day,

I own a jewellery 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's not 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?

Thanks

share|improve this question
add comment

migrated from stackoverflow.com Jan 10 '12 at 21:35

This question came from our site for professional and enthusiast programmers.

3 Answers

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.

share|improve this answer
    
Thanks, but I would like to get an opinion at this point. I have put a lot of thought into this (laugh if you will, but I'm only showing a small part of the whole thing to see if this conceptual approach is a good one to take). I'm building a Windows Phone based POS system too which will use this inventory database. –  johnmatrix Jan 10 '12 at 6:46
    
This book is my staendard reerence and it contains a lot more detailed explanations than you will ever get here. –  TomTom Jan 10 '12 at 7:13
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.