From Complexity to Clarity: Organizing Variant-Wise Product Storage for Your Online Store

Managing product variants is essential for e-commerce platforms, especially in multi-vendor setups where diverse products, attributes, and variants can quickly become complex. In this post, we’ll explore a structured approach to organizing and storing product variants in a way that’s scalable and easy to manage.

Step 1: Create an Attributes Table

The first step is to create a central Attributes table to hold general attributes that apply to products across the platform. Attributes like size, color, and material give customers flexibility and allow you to customize each product.

Attributes Table Example:

Each row here represents an attribute category, which can later be linked to individual products as needed.

Attributes Values Table

Once you’ve set up the Attributes table, it’s time to add an Attributes Values table. This table stores specific values for each attribute. For instance, if "Color" is an attribute, then "Red," "Blue," and "Green" would be values under that attribute. By structuring it this way, we keep things organized and flexible.

Attributes Values Table Example:

This setup allows each product to have multiple possible values for each attribute, and it keeps data centralized and easy to manage.

Step 2: Create the Product Table

With the attributes structured, the next step is to set up the Product table, where each row represents a unique product in the catalog. This table holds general product information, like the name, description, and base price, which will be consistent across all variants.

Product Table Example:

The Product table is intentionally broad, allowing it to represent various types of products.

Step 3: Build the Product Variant Table

Now for the core piece of the puzzle: the Product Variant table. Each row here represents a unique variant of a product, combining specific attribute values like color, size, or material. This setup enables detailed management and storage of each product variant.

Product Variant Table Example:

The Product Variant table links each variant back to the Product table, making it easy to retrieve all variants of a given product and keeping things manageable even with a large product catalog.

Modern SQL databases now offer support for JSON data types, which can be a game-changer for efficiently handling product variants. By storing variant data in JSON format, we gain flexibility, scalability, and ease of retrieval, which is especially useful in an e-commerce setting where a single product might have multiple variants.

Step 4: Storing The Variant Data into Product Table & Product Variant Table.

JSON Storage for Product Table

With a JSON-compatible SQL database, we can store each product’s variants in a variants column as a JSON array. This structure captures each attribute (such as color, size, and material) and its values in a structured format, allowing us to:

1. Organize variants by attribute type.
2. Retrieve each attribute in a specific sequence.
3. Expand or modify variant attributes without altering the table structure.

In this structure, each product’s variant attributes are stored in a JSON format that allows for flexible and intuitive retrieval.

Product Variant Storage for Product Variant Table

In this database structure, each variant is stored as a single row in a Product Variants table, with a few key fields:

• product_id: Links each variant to a main product.
• variants: A concatenated string representing specific attributes, such as size, color, and material (e.g., s-red-wool).
• price: The price of this particular variant.
• quantity: The available stock for this variant.

Here’s an example of the Product Variants table layout:

Each row represents a unique combination of size, color, and material for a product with product_id = 5. By using a single variants column for attributes, we keep our table straightforward and easy to manage.

Sending Variant Data to the Frontend

To display variant options for a specific product, the application can retrieve the variants JSON data and send it to the frontend. The frontend will receive the data organized by attributes:

• Color: Red, Blue, Green
• Size: S, M, L
• Material: Wool, Cotton

This JSON-based setup means that each attribute can be displayed in sequence on the frontend, allowing customers to make a specific selection, such as "Red-M-Wool."

Processing Selected Variant Combinations

The frontend can send a selected variant combination to the server in a structured format, such as Red-M-Wool. Here’s how this works in practice:

1. Receive User Selection: Suppose the user selects “Red-M-Wool” as their choice of color, size, and material.
   
   
2. Query the Database: The backend matches this selection to the Product Variant table. 
   

   SELECT price, quantity
   FROM product_variants
   WHERE product_id = 5
     AND variants = 'red-m-wool';

3. Return Price and Stock Availability: Once the matching variant is found, the backend retrieves the price and stock quantity for that variant and sends this data back to the frontend.

Here’s how you can manage, store, and retrieve product variants in your e-commerce platform with the given database design.

This structure is simple and effective for retrieving specific variants quickly. Benefits include:

• Efficiency: By storing attributes in a single variants string, querying specific variants is straightforward.
  
  
• Easy to Manage: Only two tables are needed to store variants, simplifying database management.
  
  
• Performance: Exact string matching is fast and allows for quick retrieval of variant data.
  
  
• Scalability: As products grow in complexity, adding variants is as simple as adding rows to the table.
  
  
• Scalable Structure: JSON allows you to add new attributes as your product catalog grows, without needing to change the database schema.
  
  
• Flexible Retrieval: JSON operators make it easy to match specific values within the JSON data, ensuring that each variant combination is accessible and manageable.
  
  
• Enhanced Frontend Integration: By organizing data in a JSON structure, you can pass attributes and values in an ordered, consistent way, creating a seamless, responsive frontend experience.

Storing product variant data in JSON format within an SQL database simplifies the process of managing complex attribute combinations. This approach is ideal for e-commerce platforms with extensive product catalogs, allowing you to store, retrieve, and display variant data in an intuitive way. JSON’s flexibility and support in SQL databases offer an efficient, scalable solution that can grow with your business.