Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

jimmy_j's avatar

What would be the best way to use this product + variations schema to practice?

I've been stuck putting the product part of this database to use and I get the logic. The issue is how does one implement this in practice...

First as it's my first day after signing up I can't link images so I'll have to write this by hand... ** the product **

PK - id
FK - category_id
name
description
images

** product_category ** This will create a hierachy of categories and subcategories

PK - id
FK - parent_category_id
category_name

** variation ** This links to the category_id in category table

PK - id
FK - category_id
name

** variation_option ** This links to variation table and has many product_configuration

PK - id
FK - variation_id
value

** product_configuration** pivot table that attaches the variation options to the product_items

FK - product_item_id
FK - variation_option_id

** product_item** basically one variant of the product (T-shirt (red with sizes Small, Medium)

PK - id
FK - product_id
SKU
qty
product_images
price

I'll list my steps on what I thought was the right way and how I ran into issues doubting this logic:

  1. create a category and have an option to link it to the parent_category. This way we create a hierarchy in this table.

  2. Create a variation ('color') and asign them to the category. ---------------> This is where my first issue is - shouldn't we be able to add color to multiple categories that use them? Like t-shirts, pants, smartphones, etc... That would mean that we need a many to many relationship as multiple categories can have multiple of the attributes and vice versa?

  3. We create a product and the user should fill in all required fields so (product name, description and image) ----------------------------------> here is the second issue: do we store this in the database and then have an option make variations of this product? But then that means if we don't have a variation added that it would be an empty product listing on the listing page. So my second solution was why not make the (create product page) a whole page of all the fields both from the product table and the product_item table?

  4. If we would go with the approach of step 2 and we use many to many that would mean I can query for all the attributes linked to the category that this product has selected and show the options on the page? for example CLOTHING has been selected then we should return color, sizes but if we have SMARTPHONE selected then we return color, storage_capacity as options.

  5. Does the user have to select atleast one of these attributes? And what if they select multiple options that would mean I need to make a new product_item for each selected option? -----------> another issue pops up what if the color dictates which sizes are available? for example a red T-shirt has Small, Medium sizes and a blue T-shirt has Small, Medium and large sizes. Maybe I'm way overthinking this but it's hard to come up with a way that satisfies all these needs...

On top of all of this I'm using Laravel (PHP) and relationships to "simplify them" but it feels like it makes it even less "readible"...

Sorry for rambling on but I've been stuck on this for days drawing on paper and writing down logic but whenever I think I got it and start putting it into practice new issues popup... I appreciate anyone reading this and feedback or your ideas would be greatly appreciated!

0 likes
3 replies
jlrdw's avatar

If you search, a while back @martinbean gave a detailed answer in a post of this very topic, product variations. I don't have the link.

Please or to participate in this conversation.