Is It Reasonable to Simplify Product Variant Design Using Notes Instead of Complex Relations?

I'm building an application where product variants are intended to be handled as physical products already prepared and listed manually. Instead of using a conventional approach with complex relations between Product, Option, OptionValue, and SKUValue tables, I'm trying to simplify the design.

💡 ERD Design:

+--------------+        +-----------------+
|   Product    |        |  ProductVariant  |
+--------------+        +-----------------+
| id (PK)      |<------>| id (PK)          |
| name         |        | product_id (FK)  |
| owner_id (FK)|        | note             | 
| created_at   |        | stock            |
| updated_at   |        | price            |
+--------------+        +-----------------+

In the ProductVariant table, the note field is a simple text field where users can manually enter descriptions like "Size: XL, Color: Red".

🔍 Django Models Based on This Design:

from django.db import models
from django.contrib.auth import get_user_model

User = get_user_model()

class Product(models.Model):
    name = models.CharField(max_length=255)
    owner = models.ForeignKey(User, on_delete=models.CASCADE, related_name='products')
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(auto_now=True)

    def __str__(self):
        return self.name


class ProductVariant(models.Model):
    product = models.ForeignKey(Product, on_delete=models.CASCADE, related_name='variants')
    note = models.TextField()  # Example: "Size: XL, Color: Red"
    stock = models.PositiveIntegerField()
    price = models.DecimalField(max_digits=10, decimal_places=2)

    def __str__(self):
        return f"{self.product.name} - {self.note}"

🎯 Why I'm Doing This:

  1. The application is designed to handle product variants that are often predefined and don't change dynamically.
  2. Users will manually input variant descriptions based on the actual physical products they have.
  3. The goal is to avoid overengineering by eliminating unnecessary tables and relationships.

🤔 What I'm Concerned About:

  • I know that most applications use well-structured relational models for managing product variants.
  • However, implementing such complex structures seems overkill for my use case, and I'm worried it might create unnecessary complications.
  • I'm concerned this simplified approach could be considered "bad design" even if it suits my use case better.

❓ Question:

Is this simplified design using manual notes for product variants acceptable in scenarios where variants are predefined and manually recorded? What are the potential pitfalls I should be aware of when using this design compared to a more "standard" relational model?

What I would think of in this situation is that now you have a column which consist of dictionary based values... And I wouldn't recommend that because of multiple reasons:

  1. validation will be harder and you might have the ability only to do soft-validation using django processing (not database hard-validation which is more consistent).

  2. size of that table will be larger in comparision with table with separated specified-role columns.

  3. having filters over text field is slower (like if you want to get all products which are red for example) .

There are cases where such columns might fit more like if you don't guarantee you always have values in options columns (sparse tables). In the case of sparse table you can have note column and you can remove null options.

The other thing is that you have to keep in mind that your design is extendable and you can always add more features to it and ofcourse you have to keep in mind requirements changing, like what if suddenly you have to change some options of the product then you will have to iterate over note column and change the values in some unconsistent way.

If the design fits your use-case, it is by definition not bad. Don't worry about best-practices if your current practice in actuality fulfills all your needs, doesn't expose you to any downsides, and is sufficiently performant.

Current design

For the note field: consider using JSONField (though note the database compatibility point) instead of TextField.

Storing the notes as structured data will make frontend parsing easier and backend searching/filtering/etc considerably easier (in particular if you're using Postgres, see the PG notes in the linked documentation). It also enables you to store more than one value-point, as well as metadata and parsing instructions (such as font size, color, or whatever you like) about the attribute.

Example:

[
    {
        "1": {
            "title": "Size",
            "us_value": "XL",
            "eu_value": 50,
            "title_color": "red",
            "value_color": "blue"
        }
    }
]

Potential pitfalls:

  • This approach will fall apart quickly if you want to let the user add complex data (i.e. any data that "exceeds" what easily fits in the key-value paradigm).
  • Data aggregation over the user-defined attributes will be cumbersome to implement, even if you're using JSONField. If you're using TextField it will be very painful for practical reasons.
  • If not using structured data like the JSON approach above, searching and filtering will be borderline impossible unless performance is absolutely no issue.
  • Bulk updates, or any situation where you need to update more than one instance at a time, will be painful both in logic and performance.

Alternate design

The "overengineered" approach would probably be something like a set of EAV-models. I know you didn't ask specifically for this, but it's always wise to know the neighboring concepts.

django-eav2 provides a good overview of EAV and its use-cases, and you could either use a library in that vein if it feels right or roll your own using generic relations.

Back to Top