+1 (843) 212-6898+8801897661858
Whatsapp-colorCreated with Sketch.
sales@mediusware.comSchedule A Call
Logo
Company
Services
Hire Developers
Industries
Case StudySTART FREE TRIALicon

About

Who We Are

Our Team

Blogs

Women Empowerment

Career

CSR

Delivery Models

Engagement Model

Services

Software Development

Web Development

Mobile App Development

E-commerce Development

Software Development

Enterprise Solutions

UI/UX Design

API Integration

Technology

React.js

Vue.js

Angular js

Laravel

Android

Flutter

iOS

React Native

Hire Developers

Hire Mobile App Developers

Hire Front-end Developers

Hire Backend Developers

Hire E-commerce Developers

Hire Developers

Hire Android Developers

Hire iOS Developers

Hire Swift Developers

Hire Flutter Developers

Hire React Native Developers

Hire Django Developers

Hire Node.js Developers

Hire Laravel Developers

We shape the art of technology
Headquarter

Headquarter

1050 Johnnie Dodds Blvd Mount Pleasant South Carolina USA ,Zip- 29464

sales@mediusware.io

+1 843-212-7149

Bangladesh Office

Bangladesh Office

24/1, Taj Mahal Road, Shiya Masjid mor, Floor - 8th & 9th, Ring Road, 1207, Dhaka, Bangladesh

sales@mediusware.com

+8801897661858

© 2025 Mediusware. All Rights Reserved

Terms & ConditionsPrivacy Policy

Table of contents

  1. Database Schema Design for Attribute Management and Variants
  2. Efficiently Storing and Retrieving Variants Using JSON Data
  3. Advantages of This Design
Share This Blog !
Get the best of our content straight to your inbox!

Don’t worry, we don’t spam!

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

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

Last Update: 30 Oct 2024

Database Schema Design for Attribute Management and Variants

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:

Attribute ID Attribute Name
1 Size
2 Color
3 Material

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:

Attribute Value ID Attribute ID Name Value
1 2 Red #00ff00
2 2 Blue #00ff33
3 1 M  
4 1 L  
5 3 Cotton  
6 3 Wool  

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:

Variant ID Product ID SKU Variant Quantiy Price
1 1 sku258 Red-Wool 10 $10.00
2 1 sku912 Blue-M 25 $11.00
3 2 sku211 Denim-Red-S 52 $42.00

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.

Efficiently Storing and Retrieving Variants Using JSON Data

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:

ID Product ID Variants Price Quantity
1 5 s-red-wool $10 25
2 5 m-blue-cotton $12 15
3 5 l-green-wool $13 10

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.

Advantages of This 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 table is 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.

 

Wrapping Up

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.

Trendingblogs
The Software Development Life Cycle (SDLC): A Complete Guide for Software Success image

The Software Development Life Cycle (SDLC): A Complete Guide for Software Success

Zustand: A Lightweight State Management Library for React image

Zustand: A Lightweight State Management Library for React

From Bugs to Beauty: Integrating UI/UX Testing into Software Quality Assurance image

From Bugs to Beauty: Integrating UI/UX Testing into Software Quality Assurance

Why is a Gaming PC the Ultimate Tool for Both Gaming and Professional Development? image

Why is a Gaming PC the Ultimate Tool for Both Gaming and Professional Development?

Why React Native is the Best Choice for Cross-Platform Development image

Why React Native is the Best Choice for Cross-Platform Development

Let's Deep Dive Into Threads with Rust image

Let's Deep Dive Into Threads with Rust

Get the best of our content straight to your inbox!

Don’t worry, we don’t spam!

Frequently Asked Questions