When working with applications that must handle a wide variety of data types or frequently changing requirements, traditional relational table designs can become cumbersome. Two common patterns for flexible data modeling are the Entity-Attribute-Value (EAV) pattern and the use of JSON columns. These approaches allow you to store attributes that may differ from one entity to another, or to accommodate sparse or unpredictable data without constantly altering your database schema.

The EAV model involves three main components: the entity (such as a product or customer), the attribute (such as color, weight, or warranty), and the value for that attribute. Each attribute-value pair is stored as a separate row, making it possible to add new attributes without changing the table structure.

JSON columns, on the other hand, take advantage of modern database support for storing semi-structured data. By using a JSON or JSONB column, you can store a set of key-value pairs directly within a single row, which can be queried and indexed in various ways.

-- EAV pattern: attributes table
CREATE TABLE attributes (
    entity_type VARCHAR(50) NOT NULL,
    entity_id INT NOT NULL,
    attribute_name VARCHAR(100) NOT NULL,
    attribute_value TEXT,
    PRIMARY KEY (entity_type, entity_id, attribute_name)
);

-- JSON column: products table already includes attributes as JSONB
-- See 'products' table in the schema init script

Using flexible data models like EAV and JSON columns comes with both benefits and trade-offs. The main advantages include:

• Supporting highly dynamic or sparse data, where different entities may have different sets of attributes;
• Reducing schema changes, since adding new attributes does not require ALTER TABLE statements;
• Enabling rapid prototyping and adaptation to evolving requirements.

However, there are important limitations to consider:

• Querying for specific attributes can be more complex and less efficient, especially with EAV where each value is a separate row;
• Enforcing data integrity and constraints is harder, since attributes are not strongly typed or validated by the schema;
• Reporting and analytics may become slower or more cumbersome, as you may need to pivot or aggregate data stored in flexible formats.

Choosing between EAV and JSON columns depends on your use case, your database's capabilities, and your need for performance versus flexibility.

-- Insert using EAV pattern
INSERT INTO attributes (entity_type, entity_id, attribute_name, attribute_value)
VALUES
('product', 1, 'color', 'silver'),
('product', 1, 'memory_gb', '16'),
('product', 2, 'color', 'black');

-- Retrieve all attributes for a product using EAV
SELECT attribute_name, attribute_value
FROM attributes
WHERE entity_type = 'product' AND entity_id = 1;

-- Insert using JSON column (attributes already present in 'products' table)
UPDATE products
SET attributes = attributes || '{"weight_kg": 2.5}'
WHERE product_id = 1;

-- Retrieve a product with a specific attribute in JSON
SELECT name, attributes->>'brand' AS brand, attributes->>'color' AS color
FROM products
WHERE attributes ? 'color';

-- Query for products with a specific JSON attribute value
SELECT name
FROM products
WHERE attributes->>'brand' = 'BrandB';