How do I create a data model that handles multiple product variations? Eg. shirt has a style and each style comes in multiple colors and sizes. Each combination (style, color, & size) is a unique item that is being invoiced.
This all depends on your pricing structure.. does the price change depending on style, colour or size?
Do you want to record what colours are sold? Many more questions like this need to be considered.
The important thing to do, is to break it down into categories and build the tables according to the categories.
eg: if the price changes, depending on size, but not colour and style, then it would be like this:
Product (product type eg: shirt, name, etc) - each product can have multiple related items, each item could be the combination of size and colour... with a price.
You may want to build a table of related colours, to make it easier when entering each item... you would only want to show the related colours when entering the items.
I hope this helps
We built a solution for a t-shirt printing shop client in which there's a related Price table to the Product table. As PeterWindle similarly stated, it allows you to have an unlimited number of price, size, color, etc. options per t-shirt product you sell. So, each line item is linked to one price record.
Keep in mind however, that most of these types of organizations also want a website checkout process which will necessitate a simpler flat file approach; i.e., one record for each iteration instead of related data.
For example: Magento does it this way:
Your base style is a Configurable Product that consists of each individual variation of the product.
Every item is a record on its own but is linked to the base product. Individual items are not visible in the web store.
They can be configured at the moment the base product is selected.
I think the items should be grouped by a unique style so you can choose different options whenever selecting within same style.
IMHO, it's the right approach to have them separate. Each item is unique depending on size and color, not the price.
Item description should be unique as well, at least within the style, size and color being part of the description for example.
Having separate fields for attributes as color, size, etc, will be handy for sorting / grouping.
Whatever you choose for, think about how you will need to track the inventory from purchase/ manufacturing to shipping. Which primary keys will those be.
Peter is correct. The pricing of variations is probably a key here. Many online stores may give you this option per variation, but not all need it.
If you need to price the variations separately, then there is a Pricing table for that.
Inventory is the other key. Counting the addition (or deduction) by variation (color & size are the most common), then you need to make the inventory table account for the variations.
Thank you for all the feedback. I would like to know your opinion of the following erd:
I would like to use a transaction ledger to monitor the inventory.
what is the relationship between ProdVariation & Price? it appears there may be MANY prices per variation combo. It that correct? what other factor might make this so?
Otherwise, your ProdVariation could have the Price in the record, correct?
Green Sweater Size 6 is one price? or is the price based on the Product Size only? if so, then the placement should be a join there and add the color in the ProdVariations.
A little more thought, please.
p.s. do you really call your "relationships" as above or something more "human readable", such as "ProdVar_InvLine" instead of "Relationship7"?
Hi Beverly, product price changes over time thus the one-to-many relationship and pricing depends on product style and size combination (eg. shirt1 XL $5, shirt1 2XL $10).
That's just the default naming of the program I'm using. I'm just trying to connect things out so I didn't change it.
Ok, price changes over time. So there is a need to track this as well, and good choice to have a separate table.
When you 'get' the price for the invoice item, you are using (or will be) the LOOKUP and not the relationship, correct? "in time works best with a lookup "copy" of the price as it may change. Then you've also got the data in the Invoice Items if pricing is a factor for "inventory" and other accounting.
I guessed you would be changing the name of the relationship once in FM! I wanted others viewing the thread to think about naming as well.
Retrieving data ...