Not All Databases Are Designed the Same

I’ve recently been revisiting an older database concept, that quietly went out of vogue when the “normalized database” became the de-facto standard for most AS/400 shops… back in the *cough*… let’s just agree it was a long time ago when my hair was still brown (not the silver fox look I’ve worked so hard to attain). Anyway, I’ve been considering how to modernizer suchj a database layout. So, let’s talk about EAV!

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?

In a traditional design, if you want to store laptops you create a laptop file:

CREATE TABLE LAPTOPS (
   SERIALNO     CHAR(20),
   MANUFACTURER CHAR(30),
   RAM_GB       DEC(5,0),
   CPU          CHAR(30),
   PURCHASEDATE DATE
);

Columns are columns. Simple.

An EAV design flips that on its head. Instead of defining the fields in the database, you store the definitions of the fields as data. A typical setup has a metadata hierarchy, something like this:

CATEGORY   (the business area, e.g. "Equipment")
   |
   +-- GROUP   (the logical "table", e.g. "Laptops")
          |
          +-- FIELD   (the logical "column", e.g. "Serial Number")

And then the actual values live in one generic file, where every attribute is a row:

RecordID  FieldID  Value
--------  -------  ----------
1         101      ABC123
1         102      Lenovo
1         103      32
1         104      i7
1         105      2026-01-15

One laptop, five rows. Every “column” is really just a row pointing at a field definition.

Why on earth would anyone do that?

Here’s the bit that gets lost when modern developers sneer at EAV. Back in the day, on the AS/400, it was often a very clever answer to a very real problem.

Think about what changing a database meant in the 80s and 90s:

Level checks were brutal. Add a field to a physical file and every RPG program using it hits a level check (CPF4131, hello old friend). Recompile everything, test everything, coordinate a weekend install window. For a busy shop, a single new field could take weeks.

Change management was slow and risky. CHGPF, logical files, rebuilt access paths, and the endless dance of saving data, recreating files and copying data back. Nobody did it lightly.

Users always want “just one more field”. Education, insurance, equipment tracking: these businesses constantly needed new bits of information captured. A new course needs an exam date. A new qualification needs an instructor and a score.

EAV made all of that go away. Need a new “table” for PMP students with exam date, instructor and score? Add a group and three field records. No DDS changes. No CHGPF. No recompiles. No level checks. The application just reads the dictionary and carries on.

The advantages for RPG programmers

For the RPG developer, a well-built EAV system was a gift:

• Write it once. One set of generic maintenance programs could handle any group. A single subfile program, driven by the field dictionary, could display, add and change records for laptops, students or courses alike.
• Fewer objects to manage. A handful of physical files instead of hundreds. Smaller libraries, simpler saves, easier object management.
• Configuration instead of coding. Business analysts or power users could define new data without waiting for a developer. That freed programmers up for real work.
• Rock-solid programs. Because the file layouts never changed, programs ran for years without a recompile. Fewer changes meant fewer bugs.
• Built-in documentation. The metadata files were a data dictionary. Want to know what fields a group has? Read the field file.

The advantages for application designers

For designers, EAV offered something that was genuinely hard to get on the platform: a data-driven application. Screens, validation and even menus could be generated from the metadata. You could build a product once and sell it to many customers, each configuring their own categories and fields. Plenty of successful packages on the AS/400 were built on exactly that idea.

Nothing is free, though

The flexibility came at a price, and it’s a price we’re still paying in many legacy systems:

• Queries get complicated. “Find all laptops with more than 16GB RAM” becomes a series of joins to locate the RAM field and then compare a value that’s probably stored as text.
• Values are usually stored generically, so you lose real data types, constraints and referential integrity.
• Reporting means pivoting rows back into columns, which is slow and awkward, and confusing for anyone who isn’t the original developer.
• The database can’t help you. Db2 doesn’t know what the data means, so validation lives scattered through the application code.

What about today?

Here’s the thing. Most of the problems EAV was invented to solve don’t really exist on modern IBM i. With DDL-defined tables, SQL access and modern RPG, adding a column with ALTER TABLE is routine and rarely triggers the old recompile nightmare. And Db2 for i gives us views, JSON support, constraints and a fantastic query optimizer.

So when modernizing an EAV-based system, the sweet spot is usually a hybrid:

1. Keep the metadata dictionary. It’s valuable documentation and can drive tooling.
2. Promote the stable, heavily used groups (students, courses, enrolments, results) into proper normalized SQL tables with real types, keys and indexes.
3. Keep a small, typed EAV or JSON extension only for the genuinely ad hoc fields.
4. Put SQL views over what remains, so Node.js services, reporting tools and new developers see clean, readable data.

Final thoughts

EAV isn’t a mistake, it’s a design from a different time. The developers who built these systems were solving real constraints with real ingenuity, and many of those applications have run reliably for decades. Our job today isn’t to mock the old design but to understand why it was built that way, keep what still works, and bring the rest into the modern world.

Not all databases are designed the same, and that’s exactly what makes legacy modernization so interesting.

Have you inherited an EAV system on IBM i? I’d love to hear how you tackled it. Drop a comment below.