Custom Attributes in Relational Databases

Learn five proven approaches to handle custom attributes in relational databases (RDBMS) — from EAV and JSON columns to dynamic schema automation. Understand their pros, cons, and when to use each.

Every modern product needs flexibility — users want to add their own fields, labels, or attributes without waiting for a database migration. The problem? Relational databases weren’t built for change.
This article explores five ways to bring flexibility to RDBMS, from JSON and metadata-driven models to fully automated schema evolution, so you can support dynamic attributes without sacrificing performance.

1. Dynamic Metadata Tables

Concept

This pattern defines attributes via a metadata table and stores values separately, introducing control and typing. This approach allows you to have full flexibility in terms of the number of attributes. 

Sample schema:

CREATE TABLE AttributeMeta (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  dataType VARCHAR(50),
  entityType VARCHAR(50),
 defaultValue
);

CREATE TABLE EntityAttrValue (
  entityId INT,
  attrId INT,
  value TEXT,
  FOREIGN KEY (attrId) REFERENCES AttributeMeta(id)
);

Pros

• Structured flexibility
• Easier validation and evolution

Cons

• Requires joins and dynamic queries
• Moderate performance overhead; individual attribute indexing is not possible.
• Application logic must manage types and defaults.

Best for: configurable applications (CRMs, ERP modules) with admin-defined attributes.

2. Entity-Attribute-Value (EAV) Model

Concept

This is an extended model of option 1. The EAV model stores attributes as rows instead of columns — offering flexibility without altering the schema. This model uses entity-specific tables rather than a common table. Can also be used in combination with a metadata table to provide more control on attribute behavior (name, defaults, type safety, etc).

Sample schema:

CREATE TABLE ProductAttributes (
  product_id INT,
  attribute_name VARCHAR(100),
  attribute_value VARCHAR(255)
);

Sample data:

Pros

• Supports unlimited attributes per entity
• No need for schema migrations
• Perfect for metadata-driven designs

 Cons

• Queries are complex (pivoting required)
• Weak typing (everything stored as text)
• Poor performance at scale

Best for: Heavy systems where attributes differ widely across entities.

3. JSON Columns — Flexible Schema Inside RDBMS

Concept

Modern databases like MySQL 8+, PostgreSQL, SQL Server, and Oracle support JSON columns, combining relational and NoSQL flexibility. This gives more flexibility by supporting JSON and, hence, nested columns.

Sample schema:

ALTER TABLE Product ADD COLUMN custom_attrs JSON;
 INSERT INTO Product (id, name, custom_attrs)
VALUES (1, 'Table', '{"color": "brown", "material": "wood"}');

Query example:

SELECT id
FROM Product
WHERE JSON_EXTRACT(custom_attrs, '$.color') = 'brown';

Pros

• Highly flexible, no schema changes
• Can index JSON paths (e.g., MySQL-generated columns)
• Ideal middle ground for modern apps

 Cons

• Weak enforcement of data types
• Complex JSON path queries
• Limited referential integrity

Best for: SaaS apps and analytics systems needing customizable data models.

4. Hybrid Approach (Core + Extended Attributes)

Concept

Keep core attributes as regular columns and custom fields in a JSON or metadata table.

Sample schema:

CREATE TABLE Product (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  base_price DECIMAL(10,2),
  custom_attrs JSON
);

Pros

• Structured + flexible coexistence
• Indexed core data, extensible attributes
• Smooth evolution path from legacy schema

Cons

• Slightly more complex ORM/data layer
• Requires discipline to prevent data sprawl

Best for: enterprises evolving from rigid schemas to flexible, configurable architectures.

5. Dynamic Column Addition via Metadata-Driven Schema Evolution (Automated DDL)

Concept

Automate schema evolution by dynamically adding columns and indexes using metadata and procedures — effectively treating schema as data.

Sample schema:

CREATE TABLE AttributeMeta (
  id INT AUTO_INCREMENT PRIMARY KEY,
  attrName VARCHAR(100),
  dataType VARCHAR(50),
  dataLength INT,
  isIndexed BOOLEAN
);

Procedure example:

CALL USP_ADD_CUSTOM_ATTRIBUTE(
  pi_caName := 'color',
  pi_entityID := 101,
  pi_dataTypeId := 1,  -- VARCHAR
  pi_dataLength := 50
);

Generated DDL:

ALTER TABLE Product ADD COLUMN color VARCHAR(50) ;
CREATE INDEX idx_product_color ON Product(color);

Pros

• Full typing and native performance
• Easy querying (no JSON or joins)
• Automation avoids manual schema ops

Cons

• May have DDL locks if frequent updates (Depends on underlying DB and type of changes)
• Schema can grow large over time
• Requires orchestration for multi-tenant systems

Best for: high-performance enterprise systems with infrequent schema changes (e.g., telecom, finance).

Comparison Matrix

Choosing the Right Approach

There’s no one-size-fits-all pattern — your decision depends on data volatility, query complexity, and system scale.

• For flexibility-first systems: Use EAV or JSON columns.
• For speed and structure: Prefer dynamic column addition or hybrid models.
• For balance: Go with metadata tables.

Final Thoughts

Designing for extensibility in RDBMS is a balancing act between schema control and runtime flexibility. The ideal solution often evolves: teams start with JSON or metadata-driven models for agility, then move toward automated schema management for performance and maintainability.