product variants for webshop
Hello! I have been working on a webshop with php and mariadb(mysql). Im working on the product page but im having struggles with product variants.
With product variants i mean that one product could have different variants. for example: one hoodie can be in different colors and sizes. One phone can be in different colors, different ram and storage.
for the database tables, i have a table for just products that i use for the product list and the idea was to make separate tables for variants that's linked to the an product item.
my first idea for the variants was to make 2 tables. variantName and variantValue. they would look like this ish
variantName
-variantName_id (pk)
-product_id(fk)
-variantName_name
variantValue
-variantValue_id(pk)
-variantName_id(fk)
-variantValue_value
VariantName is linked to the product, variantValue is linked to the variantName.
if the product was a hoodie. in variantName i could add color and size and in the variantValue i could add green, blue red and S, M, L. i'd list the names in select tags and the values in option tags
The problem with this idea is that theres no way (so far i know) to link the 2 attributes together. i can select a color, but i cannot check what sizes are available for the selected color. That's where im stuck at right now. I was wondering if someone could give me any tips that could help me out
worth pointing out that the webshop isnt about anything specific so I need it to be a dynamic structure that could work for different attributes for different products.
My other idea was to limit the amount of attributes up to 3 or something, which could make it easier? but i wanted to ask here first, because i rather not set a limit
Thank you for your time :)
14 Replies
how about you normalize the data?
you can create a product, but, depending on what you sell, it can be of multiple variants:
- red t-shirt size m
- 32cm wheel mountain bike with read and front disk breaks
- a pitbull dog figurine of a collection of multiple animals
you will always have to have the variants separated from the main product
it's an n:n relationship, which dictates that you have to have a table in the middle
you can even be smart and create templates:
- a general type of item is picked to be added (clothes, electronics, sports, ...)
- a more specific group is picked (female clothes, baby clothes, computers, ...)
- each group automatically has a set of attributes ready (color and size for clothes, for example)
- each item can have a few/all attributes - making it a variant
and in the end, you always store the same for each existing item: what it is, it's attributes and a table making the connection between both
Thanks a lot for the replies! I never considered creating templates. im going to try the extra table for the n:n relationship. appreciate the time and help :)
you're welcome
I have been messing around with adding an additional table, but i cant quite figure it out. my db tables look like this atm
ive got a db table for products with basic info
ive got a variants_name table
variant_values table
and a product variants table. which links the 2 tables together
but i feell ike it doesnt help me with the problem that i dont know how to make a check whether which sizes are available for which colour (in this example). i dont want it to be just about colours and sizes, neither do i want a limit on the amount of attributes i can give a product variant. i want it as dynamic as possible, but im starting to believe its a bit too complicated for me haha. i can find very little about this on the internet which doesnt help lol
the idea is to make separate select option tags for each variant_name and using ajax to modify the options depending what is clicked on
you're going about it "the wrong way"
you have a product, you have an attribute (color, size)
simply put a table with the product id and attribute id
thats all
make both a primary key, so it is always unique
Would you mind writing down an example of how the attributes table would look like in this idea?
its literally a table with 2-3 columns:
primary id of product
primary id of attribute
(optional) sku
thats it
apologies for the radio silence. i have limited time to work on it. i couldnt get it to work, so i decided give a limit to the max amount of attributes, which is 3. i tried a new approach with the new limit and i managed to make things work!
Thanks a lot for the time and help, its much appreciated :)
you're welcome