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:
Attribute
Value
Instead of creating a table with lots of columns, all data is stored as rows.
Traditional table
+----+---------+-------+--------+
| ID | NAME | COLOR | WEIGHT |
+----+---------+-------+--------+
| 1 | Widget | Red | 10 |
+----+---------+-------+--------+
EAV model
+-----------+-----------+--------+
| 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.
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:
| Part | Meaning | Example |
|---|---|---|
| Entity | The thing being described | Student #1 |
| Attribute | The property | Exam Date |
| Value | The data | 2026-01-15 |
EAV systems usually have a metadata hierarchy that works like a dictionary:
└─ 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:
-------- ------- ----------
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.
- Level checks. Changing a physical file's record format caused
CPF4131level-check errors in every program that used it, so every program had to be recompiled and retested. - Painful file changes. Before modern
CHGPFand SQLALTER TABLE, adding a field often meant saving data, recreating files, rebuilding logicals and copying data back, usually in a weekend outage window. - Constantly changing requirements. Education, insurance and equipment businesses kept needing new fields for new courses, products or regulations.
- Packaged software. Vendors wanted one product that many customers could configure without custom code for each.
- 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:
EAV:
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:
- Profile the data. Find which groups and fields are stable and heavily used.
- Promote the core to normalized DDL tables with real types, keys, constraints and indexes.
- Keep the dictionary as documentation and for generating DDL.
- Keep a small extension for truly ad hoc fields, using typed value columns or a
JSONcolumn. - Add SQL views that pivot the remaining EAV data, so Node.js services and reporting tools see clean tables.
- Migrate gradually and run the old and new structures in parallel until they reconcile.
7. Check your understanding
- In EAV, what does a column in a normal table become?
- Name two AS/400 constraints that made EAV attractive.
- Why is indexing a single attribute hard in EAV?
- Give one case where EAV is still a good choice.
- What's the "hybrid" modernization approach?
Answers:
- A row in the value table.
- Any two of: level checks and recompiles, disruptive file changes, frequent new requirements, configurable vendor packages.
- All attributes share one generic value column.
- Sparse or user-defined custom fields.
- 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.
