EAV Model vs PostgreSQL JSONB: Pharmacy Catalog Design

When building a pharmacy e-commerce product catalog, the choice between EAV model and PostgreSQL JSONB comes down to a fundamental tension: EAV gives you relational rigor at the cost of complexity, while JSONB offers flexibility that demands discipline elsewhere. For healthcare applications where a wrong dosage attribute could have real consequences, neither approach is a silver bullet — but JSONB paired with robust application-level validation (Zod, Joi, or similar) has become the pragmatic choice for most modern pharmacy platforms, provided you understand what you’re giving up and what you must enforce in code.

Contents

• EAV vs JSONB: Core Concepts for Pharmacy Catalogs
• Schema Flexibility and Validation
• Query Performance for Complex Filtering
• Long-term Maintainability
• Data Integrity in Healthcare Systems
• Application-Level Validation: Zod, Joi, and Beyond
• Sources
• Conclusion

EAV vs JSONB: Core Concepts for Pharmacy Catalogs

Let’s start with what each pattern actually looks like under the hood.

The EAV model (Entity-Attribute-Value) decomposes product attributes into three tables: one for products (entities), one for attribute definitions (like “active_ingredient,” “dosage_mg,” “brand”), and a third that stores the actual values as rows. A single product with 20 attributes generates 20 rows in the value table. This is the classic “rows, not columns” approach — it’s how you’d model variability in a strictly relational world.

PostgreSQL JSONB, on the other hand, stores the entire attribute set as a binary-optimized JSON document inside a single column. One product, one row, one blob of structured data. The PostgreSQL JSONB documentation describes it as storing data in a decomposed binary format that supports fast indexing and querying. No separate attribute tables. No joins to reconstruct a product.

Why does this matter for pharmacy? Because a pharmacy catalog isn’t like a shoe store. You’ve got prescription drugs, OTC medications, supplements, medical devices — each with wildly different attribute profiles. A blood pressure monitor needs “measurement_range_mmhg” and “cuff_size_cm.” A vitamin D supplement needs “iu_per_serving” and “form” (softgel, tablet, gummy). An EAV model handles this naturally through its attribute registry. JSONB handles it by… just letting you put whatever you want in that column.

That “whatever you want” part is where the tension lives.

Schema Flexibility and Validation

Here’s the uncomfortable truth: JSONB is schema-less only at the database level. The moment you’re building a real application, you need schema somewhere — the question is just where it lives.

What EAV gives you

With EAV, your attribute definitions table is your schema. You can declare that “dosage_mg” must be a numeric type, that “active_ingredient” is required for all pharmaceutical products, and that “expiration_date” follows a date format. The database enforces data types on the value column (or across multiple typed value columns), and foreign key constraints tie every value to a valid attribute definition. Adding a new attribute? Insert a row into the attributes table. Removing one? Mark it inactive. The schema is data-driven, which means it’s auditable, queryable, and visible to DBAs.

This matters in healthcare. When a regulator asks “what attributes do you store for prescription medications?”, you can query the attribute definitions table and give them a definitive answer. Try doing that with raw JSONB documents scattered across millions of rows.

What JSONB gives you

JSONB, as documented in the PostgreSQL datatype reference, enforces exactly one thing: valid JSON syntax. It will reject malformed JSON, and it will reject numbers outside PostgreSQL’s numeric range. That’s it. It won’t enforce that “dosage_mg” exists. It won’t ensure “active_ingredient” is a string. It won’t prevent someone from storing "dosage": "a lot" instead of "dosage_mg": 500.

But here’s the thing — that flexibility is genuinely powerful. When your pharmacy expands into homeopathy and suddenly needs 15 new attributes that don’t apply to any other category, you just start writing them into the JSONB document. No schema migration. No attribute registry updates. No committee meeting about whether “potency_ch” deserves its own column.

The trade-off is clear: EAV makes schema explicit and database-enforced; JSONB makes schema implicit and application-enforced. For a pharmacy platform, “application-enforced” means your validation logic must be bulletproof. More on that later.

Query Performance for Complex Filtering

This is where the comparison gets genuinely interesting — and where JSONB has a significant advantage over naive EAV implementations.

The EAV filtering problem

Imagine a user filtering your pharmacy catalog: “Show me all ibuprofen products between 200mg and 400mg dosage from brands X, Y, or Z.” In EAV, this query requires multiple self-joins on the value table — one join per attribute you’re filtering on. For three filters, that’s three joins against a potentially massive table. The query plan gets ugly fast.

-- Simplified EAV filtering — gets worse with each additional filter
SELECT p.* FROM products p
JOIN product_values v1 ON p.id = v1.product_id
JOIN attributes a1 ON v1.attribute_id = a1.id AND a1.name = 'active_ingredient'
JOIN product_values v2 ON p.id = v2.product_id
JOIN attributes a2 ON v2.attribute_id = a2.id AND a2.name = 'dosage_mg'
WHERE v1.value_text = 'ibuprofen'
 AND v2.value_numeric BETWEEN 200 AND 400;

Each join adds overhead. Index strategies become complex because you’re indexing the same value column for different semantic meanings depending on the attribute_id. Query optimizers sometimes struggle with the cardinality estimates. Performance degrades as filter complexity grows.

JSONB with GIN indexes

JSONB flips this equation. PostgreSQL’s GIN indexing support for JSONB is genuinely sophisticated. You create one index on the JSONB column, and suddenly you can filter on any combination of attributes without additional joins.

The GIN index supports two operator classes for JSONB, and the choice matters:

• jsonb_ops (default): Supports the broadest set of operators including ? (key exists), @> (containment), and @@ (path queries). Good for general-purpose querying where you might search by top-level keys or arbitrary paths.
• jsonb_path_ops: Supports fewer operators but produces smaller, faster indexes for containment-style queries. If most of your filtering follows the pattern “does this document contain these specific key-value pairs?”, this is your best bet.

-- Create a GIN index optimized for containment queries
CREATE INDEX idx_product_attrs ON products USING GIN (attributes jsonb_path_ops);

-- Filter: ibuprofen, 200-400mg, specific brands
SELECT * FROM products
WHERE attributes @> '{"active_ingredient": "ibuprofen", "dosage_mg": 300}'
 AND attributes @> '{"brand": "Advil"}';

Wait, the range query on dosage is tricky with pure containment. In practice, you’d combine GIN for broad filtering with expression indexes for range queries:

-- Expression index for numeric range filtering
CREATE INDEX idx_dosage ON products (((attributes->>'dosage_mg')::numeric));

-- Now range queries work efficiently
SELECT * FROM products
WHERE (attributes->>'dosage_mg')::numeric BETWEEN 200 AND 400
 AND attributes @> '{"active_ingredient": "ibuprofen"}';

The performance difference in real pharmacy catalogs is substantial. I’ve seen EAV-based catalogs with 50,000+ products struggle with 4-5 attribute filters, while equivalent JSONB implementations with proper indexing return results in under 50ms. The lack of joins is the killer advantage here.

One caveat from the PostgreSQL docs: JSONB documents should represent atomic datums to minimize lock contention during updates. If you’re storing the entire product attribute set in one JSONB column, concurrent updates to different attributes within the same document will block each other. For a pharmacy catalog where products are updated far more often than they’re purchased, this is usually acceptable — but worth knowing about.

Long-term Maintainability

Three years from now, when the original developers have moved on and a new team is debugging why “some supplements show dosage in mcg and others in iu” — which approach serves them better?

EAV’s maintenance burden

EAV looks clean in textbooks. In production, it accumulates technical debt like a magnet:

• Attribute sprawl: Over time, the attributes table fills with deprecated, duplicated, or ambiguously-named attributes. “strength” vs “dosage” vs “concentration” — which one does the frontend use?
• Query complexity: Every new report, every new filter, every new admin feature requires wrestling with multi-join EAV queries. Your ORM probably hates it. New developers definitely hate it.
• Migration pain: Changing an attribute’s data type (say, splitting “dosage” into “dosage_value” and “dosage_unit”) requires updating potentially millions of rows in the value table, and you need to coordinate this with the attribute definitions.
• N+1 problem: Loading a product with its attributes typically requires either a massive denormalized query or N+1 fetches. ORMs handle this poorly.

The attribute definitions table becomes a kind of shadow schema that lives outside your normal migration tooling. It’s metadata about metadata, and it tends to drift.

JSONB’s maintenance profile

JSONB avoids the join complexity and attribute registry overhead. But it introduces its own maintenance challenges:

• Schema drift across documents: Without enforcement, products in the same category can evolve different attribute shapes over time. Product A has {"dosage_mg": 500}, Product B has {"dosage": {"value": 500, "unit": "mg"}}, Product C has {"strength": "500mg"}. This is the “flexibility tax.”
• Discoverability: You can’t query “what are all possible attributes used by products in category X” without scanning documents. JSONB supports this with jsonb_object_keys and lateral joins, but it’s not as clean as SELECT * FROM attributes WHERE category_id = X.
• Refactoring is scarier: Renaming an attribute in EAV is one UPDATE on the attributes table. In JSONB, it’s a bulk update across every document that contains the key — and if you miss some, you’ve got silent inconsistency.

The pragmatic middle ground that many pharmacy platforms adopt: JSONB for storage, but maintain a “schema contract” in application code (more on this in the validation section). This gives you the query performance and simplicity of JSONB while keeping the schema documented and enforceable.

Data Integrity in Healthcare Systems

This is the dimension that makes pharmacy different from other e-commerce. If a clothing store has a missing “color” attribute, a product page looks weird. If a pharmacy platform has a missing “contraindication” attribute or an incorrect “dosage_mg” value, someone could get hurt.

What EAV enforces

EAV provides several integrity mechanisms at the database level:

• Attribute existence constraints: You can require that certain attributes exist for products in specific categories (via trigger or application logic on the attribute registry).
• Type safety: The value table enforces that numeric attributes get numeric values, dates get dates, etc.
• Referential integrity: Foreign keys ensure every attribute value references a valid attribute definition.
• Auditability: The attribute definitions table provides a single source of truth for what attributes exist, their types, and their status.

What JSONB enforces

As the PostgreSQL documentation explicitly states, JSONB validates JSON syntax and numeric ranges — nothing more. It does not preserve object key order or duplicate keys (the last value wins for duplicate keys, silently). It will happily accept:

{
 "active_ingredient": null,
 "dosage_mg": -500,
 "contraindications": [],
 "brand": 42
}

Every one of those values is valid JSON. Every one is potentially dangerous in a healthcare context. A null active ingredient on a prescription drug? A negative dosage? An empty contraindications array? A brand stored as a number when your frontend expects a string?

This is why the “schema-less” question matters so much for healthcare. The database will not catch these problems. Your application must.

But here’s the counterargument that’s increasingly accepted in practice: database-level constraints are necessary but not sufficient for healthcare data integrity. Even with EAV, you need application-level validation for business rules like “dosage_mg must be positive,” “active_ingredient must reference the FDA active ingredient database,” “expiration_date must be in the future.” The database can enforce types and existence; it can’t enforce semantics.

So the real question becomes: can application-level validation achieve equivalent integrity to what EAV provides at the database level? And the answer is yes — but only if you’re disciplined about it.

Application-Level Validation

This is where frameworks like Zod (TypeScript/JavaScript), Joi (JavaScript), Pydantic (Python), and Hibernate Validator (Java/JPA) enter the picture. They’re the bridge between JSONB’s flexibility and healthcare’s integrity requirements.

How it works in practice

You define a schema contract in code that mirrors what EAV would enforce in the database:

// Zod schema for a pharmaceutical product's attributes
const PharmaceuticalAttributes = z.object({
 active_ingredient: z.string().min(1),
 dosage_mg: z.number().positive().max(10000),
 dosage_form: z.enum(['tablet', 'capsule', 'liquid', 'topical', 'injection']),
 brand: z.string().min(1),
 requires_prescription: z.boolean(),
 contraindications: z.array(z.string()).min(1),
 expiration_date: z.string().datetime(),
 storage_conditions: z.string().optional(),
});

// Validate before write
const result = PharmaceuticalAttributes.safeParse(requestBody.attributes);
if (!result.success) {
 return errorResponse(result.error);
}

This catches every problem that JSONB won’t: missing required fields, wrong types, invalid enum values, out-of-range numbers. And it does it before data reaches the database, which means:

• You get immediate, descriptive error messages back to the user or API consumer
• Invalid data never enters the database, regardless of what client sends it
• The schema contract is version-controlled alongside your application code
• Type inference (with Zod/Pydantic) gives you compile-time safety throughout your codebase

The defense-in-depth approach

For healthcare applications, you shouldn’t rely on a single validation layer. The robust approach combines:

1. Application-level schema validation (Zod, Joi, Pydantic) — primary defense, closest to business logic
2. Database CHECK constraints on extracted JSONB fields for critical values:

ALTER TABLE products ADD CONSTRAINT chk_dosage_positive
CHECK (((attributes->>'dosage_mg')::numeric) > 0);

3. Database triggers for cross-field validation that’s hard to express in CHECK constraints
4. Integration tests that verify invalid data is rejected at every API endpoint
5. Periodic data audits — scan JSONB documents for schema violations that might have slipped through

Is this more work than EAV’s built-in constraints? Absolutely. But it’s also more flexible and more testable. You can evolve the validation schema without database migrations. You can add category-specific validation rules (supplements have different requirements than prescription drugs) without complicating your attribute registry. And you can generate API documentation directly from your Zod/Pydantic schemas.

When Zod/Joi is NOT enough

There are scenarios where application-level validation alone is genuinely insufficient for healthcare:

• Direct database access: If analysts, reporting tools, or legacy systems write to the database bypassing your application, your Zod schemas won’t run. Database constraints are your only safety net.
• Race conditions: If two services write to the same product concurrently, application validation on each service can’t prevent inconsistent intermediate states. You need database transactions and potentially advisory locks.
• Regulatory requirements: Some healthcare regulations may require database-level audit trails or constraints as part of compliance. Check your specific regulatory framework.

For most pharmacy e-commerce platforms where all writes go through the application API, Zod/Joi-level validation is sufficient. But “most” isn’t “all” — and in healthcare, the exceptions matter.

Sources

1. PostgreSQL JSONB Documentation — Official reference for JSONB data type behavior, validation rules, and storage characteristics: https://www.postgresql.org/docs/current/datatype-json.html
2. PostgreSQL GIN Indexing — GIN index architecture, operator classes for JSONB, and performance characteristics: https://www.postgresql.org/docs/current/gin.html

Conclusion

So which approach wins for a pharmacy e-commerce catalog? It depends on your team, your write patterns, and your regulatory environment — but here’s the honest assessment:

EAV is the safer choice if you need database-enforced schema, have DBAs who will maintain the attribute registry, expect direct database access from multiple systems, or operate under regulations that mandate database-level constraints. It’s more work upfront and slower at query time, but the integrity guarantees are built into the data layer.

JSONB is the better choice for most modern pharmacy platforms. The query performance advantage for complex filtering is substantial, the development velocity is higher, and the flexibility maps well to the real-world diversity of pharmacy products. The “schema-less risk” is real but manageable — Zod, Joi, Pydantic, and similar frameworks can provide application-level validation that’s arguably more rigorous than EAV’s database constraints, because it can enforce semantic business rules that SQL struggles to express.

The sweet spot? JSONB storage with a strict, version-controlled schema contract in your application layer, supplemented by targeted database CHECK constraints on the most critical healthcare fields. You get JSONB’s performance and flexibility without abandoning the integrity discipline that healthcare demands. Just don’t treat “schema-less” as “schema-free” — the schema still exists, it just lives in your code instead of your database. And in healthcare, that’s a responsibility you can’t outsource to your database engine.