Entity-Attribute-Value (EAV) Databases on the AS/400

Entity-Attribute-Value (EAV) Databases on the AS/400

Ask a room full of IBM i developers to design a database and you'll get a dozen different answers, and they'll all be right in their own way. Most of us learned the "proper" way: one physical file per business thing, a sensible key, and fields with real names and real data types. But if you've been around the AS/400 for a while, sooner or later you'll open up a system and find something completely different. A handful of oddly generic files, holding *everything*.

Welcome to the world of **Entity-Attribute-Value**, or EAV for short.

So what is EAV?

Welcome to the world of **Entity-Attribute-Value**, or EAV for short. 

EAV stands for:

Entity
Attribute
Value

Instead of creating a table with lots of columns, all data is stored as rows.

Traditional table

PRODUCTS
+----+---------+-------+--------+
| ID | NAME | COLOR | WEIGHT |
+----+---------+-------+--------+
| 1 | Widget | Red | 10 |
+----+---------+-------+--------+

EAV model

PRODUCT_ATTRIBUTES
+-----------+-----------+--------+
| ENTITY_ID | ATTRIBUTE | VALUE |
+-----------+-----------+--------+
| 1 | NAME | Widget |
| 1 | COLOR | Red |
| 1 | WEIGHT | 10 |
+-----------+-----------+--------+

Where:

  • Entity = Product #1
  • Attribute = COLOR, WEIGHT, NAME
  • Value = Red, 10, Widget

Why use EAV?

EAV is useful when:

  • Every record can have different attributes
  • You don't know all the columns in advance
  • Users can define their own fields

Examples:

  • Medical records
  • Product catalogues
  • CRM systems with custom fields
  • WordPress metadata tables (wp_postmeta, wp_usermeta) are essentially EAV structures

1. Two ways to store the same data

Normalized (traditional): each business entity gets its own table, and each attribute gets its own typed column.

CREATE TABLE STUDENT (
STUDENTID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(50) NOT NULL,
EXAMDATE DATE,
SCORE DECIMAL(5,2) CHECK (SCORE BETWEEN 0 AND 100)
);

EAV: the structure itself is stored as data. There are three parts:

PartMeaningExample
EntityThe thing being describedStudent #1
AttributeThe propertyExam Date
ValueThe data2026-01-15

EAV systems usually have a metadata hierarchy that works like a dictionary:

CATEGORY (business area) e.g. Training
└─ GROUP (logical table) e.g. PMP Students
└─ FIELD (logical column) e.g. Exam Date, Instructor, Score

The values then live in one generic file, one row per attribute:

EntityID FieldID Value
-------- ------- ----------
1 201 2026-01-15
1 202 J. Smith
1 203 87

Key idea: in EAV, columns become rows.

2. Why EAV was used on the AS/400

EAV was a practical answer to the platform's constraints in the 1980s and 90s.

  1. Level checks. Changing a physical file's record format caused CPF4131 level-check errors in every program that used it, so every program had to be recompiled and retested.
  2. Painful file changes. Before modern CHGPF and SQL ALTER TABLE, adding a field often meant saving data, recreating files, rebuilding logicals and copying data back, usually in a weekend outage window.
  3. Constantly changing requirements. Education, insurance and equipment businesses kept needing new fields for new courses, products or regulations.
  4. Packaged software. Vendors wanted one product that many customers could configure without custom code for each.
  5. Limited developer time. Letting analysts define fields as data freed up scarce RPG programmers.

EAV removed the need for most of that. A new "table" or "field" was just a few new records, with no DDS change, no recompile and no outage.

Advantages

✅ Very flexible

✅ New attributes can be added without altering tables

✅ Good when different entities have wildly different properties

Disadvantages

❌ Queries become more complex

❌ Performance can suffer on large datasets

❌ Data types are harder to enforce

❌ Reporting becomes difficult because data must be "pivoted" back into columns

For example, finding all red products weighing more than 10kg requires multiple joins to the same EAV table.

Example: find students who scored more than 80.

Normalized:

SELECT NAME FROM STUDENT WHERE SCORE > 80;

EAV:

SELECT n.VALUE AS NAME
FROM VALUES_TBL s
JOIN FIELDS f ON f.FIELDID = s.FIELDID AND f.FIELDNAME = 'Score'
JOIN VALUES_TBL n ON n.ENTITYID = s.ENTITYID
JOIN FIELDS fn ON fn.FIELDID = n.FIELDID AND fn.FIELDNAME = 'Name'
WHERE DECIMAL(s.VALUE, 5, 2) > 80;

The EAV version is longer, harder to read, can't use an index on the score, and fails if any stored value isn't numeric.

5. When EAV still makes sense

Good fit: sparse, unpredictable or user-defined attributes, such as custom fields, product specs that vary by type, or survey answers.

Poor fit: the stable core of a business. In an education system that's students, courses, enrolments, results and payments.

6. Modernizing an EAV system on IBM i

Modern Db2 for i largely removes the original reasons for EAV. ALTER TABLE is routine, and SQL access and modern RPG avoid most level-check pain. The recommended approach is a hybrid:

  1. Profile the data. Find which groups and fields are stable and heavily used.
  2. Promote the core to normalized DDL tables with real types, keys, constraints and indexes.
  3. Keep the dictionary as documentation and for generating DDL.
  4. Keep a small extension for truly ad hoc fields, using typed value columns or a JSON column.
  5. Add SQL views that pivot the remaining EAV data, so Node.js services and reporting tools see clean tables.
  6. Migrate gradually and run the old and new structures in parallel until they reconcile.

7. Check your understanding

  1. In EAV, what does a column in a normal table become?
  2. Name two AS/400 constraints that made EAV attractive.
  3. Why is indexing a single attribute hard in EAV?
  4. Give one case where EAV is still a good choice.
  5. What's the "hybrid" modernization approach?

Answers:

  1. A row in the value table.
  2. Any two of: level checks and recompiles, disruptive file changes, frequent new requirements, configurable vendor packages.
  3. All attributes share one generic value column.
  4. Sparse or user-defined custom fields.
  5. Normalize the stable core, keep a small typed EAV or JSON extension, and put views on top.

Key takeaway: EAV wasn't a mistake. It was a clever answer to the AS/400's limits at the time. Understand why it was built, keep the flexibility where it still earns its place, and bring the core data back into a properly normalized design.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
>