Best Practices

The conventions and patterns that separate junior modelers from senior ones.

Naming conventions

The single biggest source of pain in real-world data models is inconsistent naming. Pick a standard, document it, and enforce it via ERWIN's naming standards.

Entity / table names

• Singular, not plural ("customer", not "customers")
• snake_case for physical, Title Case With Spaces for logical
• No reserved words ("order" is reserved in SQL — use "customer_order" or quote it)
• No prefixes like "tbl_" (the schema is the namespace)
• Use the business name, not the technical name ("subscriber" not "user_record_v2")

Column names

• snake_case always
• Primary keys: {table}_id ("customer_id", not just "id")
• Foreign keys: use the SAME name as the referenced PK ("customer_id" in the order table)
• Booleans: "is_*" or "has_*" ("is_active", "has_subscription")
• Timestamps: "_at" suffix ("created_at", "deleted_at"), not "_date" (it's a timestamp not just a date)
• Dates: "_date" suffix ("order_date", "birth_date")
• Counts/numbers: "_count", "_amount", "_quantity", "_price"

Constraint names

• Primary key: pk_{table}
• Foreign key: fk_{child}_{parent}
• Unique: uq_{table}_{columns}
• Check: chk_{table}_{column}
• Index: idx_{table}_{columns}

Named constraints are dramatically easier to debug — error messages reference the name, not an auto-generated number.

Use surrogate keys for all dimensions

Don't use natural keys (email, SSN, product code) as primary keys. Use an auto-generated integer/BIGINT instead. Detailed reasoning here.

Audit columns on every table

Add these four columns to every business-data table:

• created_at TIMESTAMPTZ DEFAULT NOW()
• created_by VARCHAR (or FK to user table)
• updated_at TIMESTAMPTZ DEFAULT NOW()
• updated_by VARCHAR (or FK to user table)

In ERWIN, define these as a Domain or template so adding them is one click. Future-you (when debugging "why did this record change yesterday?") will thank you.

Soft deletes vs hard deletes

For data you might need to recover or audit, use soft deletes:

• Add deleted_at TIMESTAMPTZ (NULL means "active")
• Add a filtered index: CREATE INDEX ... WHERE deleted_at IS NULL
• Application queries filter WHERE deleted_at IS NULL

Hard deletes are fine for ephemeral data (logs, sessions, caches) where recovery is meaningless.

Use ERWIN's domains religiously

Don't type VARCHAR(50) in 30 places. Define a domain once, reference it everywhere. Common domains:

Subject areas — break up big models

A single diagram with 100+ entities is unreadable. Use subject area diagrams:

Common subject areas in an enterprise model:

• Customer Management — customer, address, contact, preference
• Catalog — product, category, brand, supplier
• Order Management — order, order_line, payment, fulfillment
• Inventory — warehouse, stock, transfer, adjustment
• Audit / System — user, role, permission, audit_log

An entity can appear in multiple subject areas — useful for "boundary" entities like customer or product.

Common patterns to know

Lookup tables

Don't hardcode status values as strings. Create a lookup table:

Audit log pattern

Generic audit trail for any record change:

Polymorphic associations (use carefully)

When a child can belong to one of several parent types:

ERWIN can't draw proper FKs for this — the relationship is enforced in application code. Convenient for some patterns, but you lose database-level referential integrity. Use sparingly.

Pitfalls to avoid

❌ Storing comma-separated values

Never. tags VARCHAR(200) = "red,large,sale" is wrong. Always normalize to a related table (product_tag with one row per tag).

❌ Magic numbers as status

Don't store order_status INT = 3. Use a status code string ("Shipped") or a lookup table. Future-you will not remember what 3 means.

❌ Single-table inheritance abuse

"Let's put customers, vendors, and employees in one 'person' table with a type column." This works for 2 weeks then becomes a nightmare. Use separate tables; share an abstract logical entity in the LDM if needed.

❌ Mixing concerns in one column

Don't put a date and a flag in the same column. Don't put currency and amount in one VARCHAR. Each column = one fact.

❌ Forgetting indexes on FK columns

Every FK should have an index on it. ERWIN doesn't always auto-create this in some database dialects. Check after generating SQL.

❌ Not documenting

The "what does this table mean" definitions take 30 seconds to write and save hours of confusion later. Always fill them in.

Version control your models

Save ERWIN models as XML for Git compatibility:

• Binary .erwin files don't diff in Git — commits look like "binary file changed"
• XML files diff line by line — you can see exactly what changed in a code review
• Use meaningful commit messages: "Add audit columns to order table per PROJ-1234"