Designing a MySQL Database for B2B Energy Products • FarrosFR

1. Introduction#

In this article, we will walk through how to design and implement a MySQL database for a B2B project that manages electrical materials and renewable energy products.

The system will handle:

Core product data (SKU, price, stock, etc.)
Multi-level categories (category → subcategory → sub-subcategory)
Brand management
Tier pricing (volume-based pricing)
Multi-currency support (USD & IDR/Rupiah)

This design follows relational database best practices: normalization, foreign keys, indexing, and scalability.

2. System Overview#

The project is a B2B product catalog + pricing engine for materials such as:

Cables
Circuit breakers
Solar panels
Inverters
Battery systems
Switchgear
Renewable energy components

Because this is B2B, it requires:

Tier pricing (bulk discount)
Multiple currencies (USD & IDR)
Clear product hierarchy
Brand-based filtering
Stock and MOQ control

3. System Architecture Diagram (ASCII)#

System Flow (User → Database)#

+-------------------+
|   B2B Customer    |
+---------+---------+
          |
          v
+-------------------+
|   Web / API App   |
| (Laravel / Node)  |
+---------+---------+
          |
          v
+-------------------+
|   Business Logic  |
| - Pricing Engine  |
| - Tier Calculation|
| - Currency Logic  |
+---------+---------+
          |
          v
+-------------------+
|      MySQL DB     |
|-------------------|
| products          |
| brands            |
| categories        |
| tier_prices       |
| product_categories|
+-------------------+

bash

Product Data Relationship Flow (Database Structure)#

                 +----------------+
                 |    brands      |
                 |----------------|
                 | id (PK)        |
                 | name           |
                 +--------+-------+
                          |
                          |
                          v
+----------------+    +----------------+
|   categories   |    |    products    |
|----------------|    |----------------|
| id (PK)        |    | id (PK)        |
| name           |    | sku            |
| parent_id (FK) |    | product_name   |
+--------+-------+    | brand_id (FK)  |
         |            | price_usd      |
         |            | price_idr      |
         |            +--------+-------+
         |                     |
         |                     v
         |            +----------------+
         |            |  tier_prices   |
         |            |----------------|
         |            | product_id (FK)|
         |            | min_qty        |
         |            | price_usd      |
         |            | price_idr      |
         |            +----------------+
         |
         v
+----------------------+
| product_categories   |
|----------------------|
| product_id (FK)      |
| category_id (FK)     |
+----------------------+

bash

Tier Pricing Logic Flow (Business Logic)#

Customer selects quantity (Q)
              |
              v
Fetch all tier_prices
ORDER BY min_qty DESC
              |
              v
Find first tier where:
Q >= min_qty
              |
              v
Apply tier price
              |
              v
Return final price (USD / IDR)

bash

Category Hierarchy Logic#

Electrical
   |
   +-- Cable
   |      |
   |      +-- Solar Cable
   |
   +-- Circuit Breaker
   |
   +-- Renewable Energy
          |
          +-- Solar Panel
          +-- Inverter

bash

4. Database Design#

We will design the following tables:

Main Tables#

products
brands
categories
product_categories
tier_prices

Optional extension:

currencies
product_documents

5. Table Implementation#

Category Structure#

Since we have:

Category
Sub Category
Sub Sub Category

We will use a self-referencing (adjacency list) model.

categories table#

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id)
);

sql

How it works#

id	name	parent_id
1	Electrical	NULL
2	Cable	1
3	Solar Cable	2

This allows unlimited depth while keeping schema simple.

Brand Table#

CREATE TABLE brands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

sql

Products Table#

This is the core of the system.

Required Fields#

SKU
Product Name
Description / Specification
Datasheet
Price (USD & IDR)
Stock
Unit
MOQ
Brand reference

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    product_name VARCHAR(255) NOT NULL,
    description TEXT,
    datasheet_url VARCHAR(255),

    price_usd DECIMAL(15,2) NOT NULL,
    price_idr DECIMAL(18,2) NOT NULL,

    stock INT DEFAULT 0,
    unit VARCHAR(50) NOT NULL,
    moq INT DEFAULT 1,

    brand_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (brand_id) REFERENCES brands(id)
);

sql

Product–Category Relationship#

A product may belong to multiple categories.

CREATE TABLE product_categories (
    product_id INT,
    category_id INT,
    PRIMARY KEY (product_id, category_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
);

sql

6. Tier Pricing Logic#

This is critical in B2B.

Example:

1–9 units: $100
10–49 units: $90
50+ units: $80

CREATE TABLE tier_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    min_qty INT NOT NULL,
    price_usd DECIMAL(15,2) NOT NULL,
    price_idr DECIMAL(18,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

sql

Example Data#

product_id	min_qty	price_usd	price_idr
1	1	100.00	1500000
1	10	90.00	1350000
1	50	80.00	1200000

7. Query Examples#

INSERT INTO brands (name)
VALUES ('Schneider Electric');

INSERT INTO products (
    sku,
    product_name,
    description,
    datasheet_url,
    price_usd,
    price_idr,
    stock,
    unit,
    moq,
    brand_id
) VALUES (
    'MCB-SCH-16A',
    'Miniature Circuit Breaker 16A',
    '1P 16A MCB for residential and industrial use',
    'https://example.com/datasheet.pdf',
    25.00,
    375000,
    500,
    'pcs',
    10,
    1
);

sql

Get Product With Tier Price#

SELECT p.product_name, t.min_qty, t.price_usd
FROM products p
JOIN tier_prices t ON p.id = t.product_id
WHERE p.sku = 'MCB-SCH-16A'
ORDER BY t.min_qty ASC;

sql

Get All Solar Category Products#

SELECT p.product_name
FROM products p
JOIN product_categories pc ON p.id = pc.product_id
JOIN categories c ON pc.category_id = c.id
WHERE c.name = 'Solar Cable';

sql

Multi-Currency Strategy#

Two approaches:

Approach A: Store Both USD & IDR (Simple)#

✔ Fast

✔ No conversion needed

❌ Must manually update exchange rates

Approach B: Store USD Only + Exchange Rate Table#

CREATE TABLE exchange_rates (
    currency_code VARCHAR(10) PRIMARY KEY,
    rate_to_usd DECIMAL(15,6),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

sql

Then convert dynamically.

For B2B in Indonesia, storing both USD & IDR is usually practical.

10. Performance Considerations#

Add indexes:

CREATE INDEX idx_sku ON products(sku);
CREATE INDEX idx_category_parent ON categories(parent_id);
CREATE INDEX idx_tier_product ON tier_prices(product_id);

sql

For large datasets:

Use pagination
Avoid SELECT *
Consider caching tier pricing

Scaling for Real B2B#

To make it production-ready:

Add:

customers
customer_groups
Custom pricing per group
Order management
Quotation system
API layer
Soft delete column

Final Architecture Summary#

This MySQL design provides:

Structured hierarchical categories
Brand management
Multi-currency pricing
Tier pricing for bulk
Clean relational design
Scalable B2B foundation

It is optimized for:

Electrical material distributors
Renewable energy suppliers
Solar project vendors
Industrial procurement platforms