Getting Database Modeling Right

Primary keys, referential integrity, and clean relationships — with a practical focus on IBM i, Db2 for i, and modern backend architectures.

1. Database modeling is architecture

In many IBM i landscapes, databases have grown over time: DDS-described physical files, logical files, RPG programs with decades of validation logic, very few explicit foreign keys, and data integrity that was “always handled by the application.” As long as a system runs in isolation, that can work surprisingly well. The invoice usually arrives later — when integration, reporting, or modernization becomes real.

Typical situations where modeling weaknesses become visible:

• A new Java, .NET, or web front end accesses Db2 for i directly through SQL.
• A data warehouse extracts ERP data regularly through ETL or ELT pipelines.
• An older 5250 application is gradually extended with APIs, microservices, or integration hubs.
• Two companies with their own IBM i systems merge.
• Multi-tenancy or group-wide consolidation is introduced after the fact.
• External systems replicate data through CDC or synchronization processes.

At that point, “the RPG program takes care of it” is no longer enough. Direct SQL access, new services, batch jobs, and reporting tools often bypass that logic. What used to work inside a monolith suddenly becomes visible in an integrated architecture — including duplicates, orphaned child rows, unclear keys, and inconsistent status values.

2. Cutting entities the right way

An entity is not a table name. An entity is a business object with stable identity, clear responsibility, and a traceable life cycle. Tables are simply the technical implementation of that business model.

Example: sales order.

• An order is created, changed, confirmed, shipped, invoiced, canceled, and archived.
• An order line initially exists only within the context of that order.
• If the line is later referenced independently — for example by a return, warehouse document, quality process, or legal retention rule — it needs a clear identity of its own.

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    order_no VARCHAR(30) NOT NULL,
    customer_id BIGINT NOT NULL,
    order_status CHAR(1) NOT NULL DEFAULT 'O',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    CONSTRAINT uq_orders_order_no UNIQUE (order_no),
    CONSTRAINT chk_orders_status CHECK (order_status IN ('O','C','I','S'))
);

CREATE TABLE order_positions (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    order_id BIGINT NOT NULL,
    position_no INTEGER NOT NULL,
    item_id BIGINT NOT NULL,
    quantity DECIMAL(11,3) NOT NULL,
    net_price DECIMAL(15,4) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_pos_order
      FOREIGN KEY (order_id) REFERENCES orders(id)
      ON DELETE CASCADE,
    CONSTRAINT uq_pos_no UNIQUE (order_id, position_no),
    CONSTRAINT chk_pos_quantity CHECK (quantity > 0)
);

The business model comes first. If you design tables without a clear business boundary, you create ambiguous ownership: Which table is authoritative? Which column is the truth? Which process is allowed to delete? Which data must remain audit-proof?

3. Primary key: identity is not meaning

A primary key has one job: to identify a row permanently, uniquely, and stably. It is not a display field, not a business rule, and not a human-facing numbering scheme.

Why meaningful keys become expensive over time

• Mergers: Two systems may both have customer number 4711, but for different customers.
• Multi-tenancy: A simple customer_no suddenly becomes tenant + customer_no.
• Rule changes: Number formats change, prefixes are added, and old numbers still remain historically relevant.
• Replication: Decentralized systems assign numbers independently.
• Interfaces: External systems store the key and expect it to remain stable.

Recommended key strategy

Storing UUIDs sensibly on Db2 for i

On Db2 for i, GENERATE_UUID() returns a formatted CHAR(36) value. For smaller indexes and more compact storage, storing the value as BINARY(16) or VARBINARY(16) can make sense. Conversion can be done with VARBINARY_FORMAT on supported releases.

CREATE TABLE customers (
    id_bin VARBINARY(16) NOT NULL
      DEFAULT VARBINARY_FORMAT(GENERATE_UUID(),
        'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'),
    tenant_id SMALLINT NOT NULL,
    customer_no VARCHAR(30) NOT NULL,
    name1 VARCHAR(80) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    rowchg_ts TIMESTAMP NOT NULL
      GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP,
    PRIMARY KEY (id_bin),
    CONSTRAINT uq_customer_tenant_no UNIQUE (tenant_id, customer_no),
    CONSTRAINT chk_customer_no_len CHECK (LENGTH(customer_no) BETWEEN 3 AND 30)
);

-- Display the UUID in readable form
SELECT VARCHAR_FORMAT_BINARY(id_bin,
       'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX') AS customer_uuid,
       tenant_id,
       customer_no,
       name1
FROM customers;

4. Integration, multi-tenancy, and mergers

Integration is the stress test for every data model. As soon as data is moved, mirrored, consolidated, or synchronized in real time across systems, you find out whether identity and relationships are truly stable.

Common integration traps

• Overlapping number ranges: Customer 4711 exists in system A and system B — but they are different customers.
• Multi-tenancy added later: Every uniqueness rule and almost every relationship must be re-evaluated.
• Business keys used as technical coupling: Changes in the business trigger technical migrations.
• No declarative constraints: ETL or reporting processes discover inconsistencies that the application supposedly would never have allowed.
• No change strategy: Without reliable change timestamps or journal-based change capture, delta extraction and CDC become unnecessarily difficult.

5. Modernizing legacy tables on IBM i

Many classic IBM i applications are based on DDS-described files and logical files. That is not automatically bad. The problem starts when implicit assumptions are not visible as SQL constraints. Modern tools then see tables, but not the rules that keep the data clean.

A pragmatic modernization path

1. Profile existing data: duplicates, NULL values, invalid status codes, orphaned child records.
2. Add a technical ID: BIGINT IDENTITY or UUID, depending on integration requirements.
3. Populate existing rows cleanly.
4. Protect business keys with UNIQUE constraints.
5. Gradually move references to technical IDs — starting with new tables and services.
6. Enable foreign keys, CHECK constraints, and NOT NULL rules only after data cleanup.
7. Provide compatibility views so old programs do not have to be rewritten immediately.

-- Example: add a technical UUID column
ALTER TABLE customer_legacy
  ADD COLUMN id CHAR(36) CCSID 1208;

-- Populate existing rows
UPDATE customer_legacy
   SET id = GENERATE_UUID()
 WHERE id IS NULL;

-- Check data quality before adding constraints:
-- 1) Are there any NULL values?
-- 2) Are there duplicates in the business key?
-- 3) Are there dependent rows without a matching parent?

ALTER TABLE customer_legacy
  ALTER COLUMN id SET NOT NULL;

ALTER TABLE customer_legacy
  ADD CONSTRAINT pk_customer_legacy PRIMARY KEY (id);

ALTER TABLE customer_legacy
  ADD CONSTRAINT uq_customer_legacy_tenant_no
  UNIQUE (tenant_id, customer_no);

6. Referential integrity belongs in the database

Foreign-key constraints are not duplicated business logic. They are structural invariants: a child row must not point to a parent row that does not exist. That rule belongs where all access paths meet — in the database.

IBM describes referential constraints in exactly that sense: they ensure that values in a dependent table are valid only when they refer to matching values in a parent table. Once defined, they are enforced system-wide when data is changed through SQL and other interfaces.

ALTER TABLE order_line
  ADD CONSTRAINT fk_order_line_order
  FOREIGN KEY (order_id)
  REFERENCES order_header (id)
  ON DELETE CASCADE
  ON UPDATE RESTRICT;

7. Modeling relationships correctly

One-to-many — the classic case

A customer has many orders. The order references the customer. Not the other way around.

customers (id PK)
orders    (id PK, customer_id FK -> customers.id)

Many-to-many — almost never just a dumb cross-reference table

In real systems, many-to-many relationships often gain attributes of their own: quantity, price at the time, discount, status, sequence, source, validity. At that point, the relationship table is an entity in its own right.

CREATE TABLE order_item (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    order_id BIGINT NOT NULL,
    item_id BIGINT NOT NULL,
    position_no INTEGER NOT NULL,
    quantity DECIMAL(11,3) NOT NULL,
    price_at_order DECIMAL(15,4) NOT NULL,
    discount_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    CONSTRAINT fk_oi_order FOREIGN KEY (order_id)
      REFERENCES orders(id) ON DELETE CASCADE,
    CONSTRAINT fk_oi_item FOREIGN KEY (item_id)
      REFERENCES items(id) ON DELETE RESTRICT,
    CONSTRAINT uq_oi_position UNIQUE (order_id, position_no),
    CONSTRAINT chk_oi_quantity CHECK (quantity > 0)
);

8. New table or new column?

One of the most common modeling questions is: Is a new column enough — or do we need a new table?

9. EAV, JSON, and custom fields

Entity-Attribute-Value, or EAV, stores attributes as rows instead of columns. That looks flexible, but it moves structure out of the model and into the data. Typing, constraints, indexes, and reporting become much more difficult.

CREATE TABLE item_eav (
    item_id BIGINT NOT NULL,
    attribute_name VARCHAR(80) NOT NULL,
    attribute_value VARCHAR(4000),
    PRIMARY KEY (item_id, attribute_name)
);

Why EAV hurts for core data

• Loss of type information: Numbers, dates, and flags end up as text.
• Weak validation: NOT NULL, CHECK, and FOREIGN KEY rules apply only indirectly.
• Complex queries: Simple columns become self-joins, pivot logic, and MAX(CASE ...) expressions.
• Difficult reporting: BI tools expect stable columns, not dynamic lists of attributes.
• Index problems: An index on attribute_name does not automatically help value comparisons that require type conversion.

When EAV or JSON can be acceptable

• There are many potential additional attributes, most of which are empty.
• Attributes are customer-specific or tenant-specific.
• The data is not part of central integrity rules.
• Reporting is handled through controlled views, materialized structures, or defined export models.

CREATE TABLE attribute_def (
    attribute_id BIGINT GENERATED ALWAYS AS IDENTITY,
    attribute_key VARCHAR(80) NOT NULL,
    data_type CHAR(1) NOT NULL,
    required SMALLINT NOT NULL DEFAULT 0,
    PRIMARY KEY (attribute_id),
    CONSTRAINT uq_attribute_key UNIQUE (attribute_key),
    CONSTRAINT chk_attribute_type CHECK (data_type IN ('S','N','T','B'))
);

CREATE TABLE item_attribute (
    item_id BIGINT NOT NULL,
    attribute_id BIGINT NOT NULL,
    val_string VARCHAR(4000),
    val_number DECIMAL(31,10),
    val_timestamp TIMESTAMP,
    val_boolean SMALLINT,
    PRIMARY KEY (item_id, attribute_id),
    CONSTRAINT fk_item_attr_def
      FOREIGN KEY (attribute_id) REFERENCES attribute_def(attribute_id),
    CONSTRAINT chk_exactly_one_value CHECK (
      (CASE WHEN val_string IS NULL THEN 0 ELSE 1 END) +
      (CASE WHEN val_number IS NULL THEN 0 ELSE 1 END) +
      (CASE WHEN val_timestamp IS NULL THEN 0 ELSE 1 END) +
      (CASE WHEN val_boolean IS NULL THEN 0 ELSE 1 END) = 1
    )
);

10. Normalization, explained pragmatically

Normalization reduces redundancy and prevents common data anomalies. In practice, the goal is not to push every model academically into fifth normal form. The goal is to keep master data clean and use redundancy only deliberately.

order_header (
    order_no,
    customer_no,
    customer_name,
    customer_street,
    customer_zip,
    customer_city,
    ...
)

If the customer address changes, it has to be updated in every order. If only some rows are updated, the database now contains contradictory data. That is the classic update anomaly.

11. Anti-patterns at a glance

• Meaningful primary keys as the default.
• VARCHAR(5000), CLOB, or JSON as a universal type for structured core data.
• Comma-separated lists in a single column.
• Status values as CHAR(1) without a CHECK constraint or lookup table.
• NULL without clear semantics: unknown, not applicable, empty, not yet calculated?
• Columns like phone1, phone2, phone3 instead of a proper one-to-many structure.
• History tracking without valid_from, valid_to, version, or audit concept.
• Soft delete without an archive and purge strategy.
• Data integrity only in RPG, Java, or .NET.
• EAV for core data instead of true customization scenarios.

ALTER TABLE order_header
  ADD CONSTRAINT chk_order_header_status
  CHECK (status IN ('O','C','I','X'));

-- O = open, C = confirmed, I = invoiced, X = canceled

12. Practical checklist

13. Conclusion

Sources & further reading