data-modeling

name	data-modeling
description	This skill should be used when the task involves design and structure enterprise data schemas in ERP•AI -- use when defining entities, relationships, field types, validation rules, and multi-tenancy patterns for transactional and analytical workloads.
version	1.0.0
metadata	{"author":"erphq","domain":"erpai.studio","department":"information-technology","size_tier":"01-org-under-100","type":"skill","scope":"internal"}

Data Modeling

Size-Tier Scope

This variant scales the operating pattern for organizations under 100 people. Keep the controls lightweight, favor owner-led approvals, and introduce automation only where it removes recurring manual work without adding governance overhead.

Purpose

Data modeling is the foundation of every enterprise application built on ERP•AI. A well-designed schema determines how efficiently the system stores, retrieves, and enforces business rules on operational data. Builders need this skill whenever they are:

• Standing up a new module (Finance, HR, Supply Chain, CRM)
• Extending an existing module with custom entities
• Designing reporting or analytics layers on top of transactional data
• Preparing a system for multi-tenant SaaS deployment
• Defining the contract between the app layer and integrations

Poor data modeling creates compounding technical debt: bad queries, broken reports, integration mismatches, and audit failures. Getting the schema right early is the highest-leverage activity in enterprise app building.

Start Here: Frame the Schema Decisions

Before creating entities and fields, align design intent:

• Use the Fit-Gap Matrix to classify standard vs custom data structures.
• Use the Requirements Traceability template to connect each entity decision to reporting, integration, and compliance needs.
• Assign Solution Architect and Data Engineer as joint owners for model quality and operability.

Do these first actions before detailed modeling:

1. Define canonical entities, ownership, and source-of-truth boundaries.
2. Define tenant isolation and key strategy (natural vs surrogate).
3. Define audit and temporal requirements that affect table shape.

Key Concepts

Entities and Relationships

An entity represents a real-world business object -- a Customer, Invoice, Product, Employee, or General Ledger Account. Each entity becomes a table (or document) in ERP•AI's data layer.

Relationships describe how entities connect:

• One-to-One (1:1): Employee has one Tax Profile. Implemented via a shared primary key or a unique foreign key. Use sparingly -- often a sign the entities should be merged.
• One-to-Many (1:N): Customer has many Orders. The "many" side holds the foreign key. This is the most common relationship in enterprise data.
• Many-to-Many (M:N): Products belong to many Categories; Categories contain many Products. Implemented with a junction (bridge) table that holds two foreign keys. The junction table often carries its own attributes (e.g., display_order, is_primary).

Normalization

Third Normal Form (3NF) is the default target for transactional (OLTP) schemas:

• 1NF: Every field holds a single atomic value. No repeating groups. A phone number list becomes a child table, not a comma-separated string.
• 2NF: Every non-key field depends on the entire primary key, not a subset. Relevant when composite keys are used.
• 3NF: Every non-key field depends only on the primary key, not on another non-key field. Example violation: storing customer_name on the Order table instead of looking it up via customer_id.

Star Schema is the target for analytics (OLAP) workloads:

• A central fact table holds measures (amounts, quantities, counts) and foreign keys to dimension tables.
• Dimension tables hold descriptive attributes (customer name, product category, date hierarchy).
• Denormalization is intentional here -- it makes aggregation queries fast.

In ERP•AI, transactional entities follow 3NF. When analytics views or dashboards are needed, build materialized views or ETL jobs that reshape the data into star schemas.

Field Type Selection

Choosing the correct field type prevents data quality issues downstream.

Business Need	Field Type in ERP•AI	Why
Free-form text (name, description)	string / text	Use string (max 255) for short values, text for long-form.
Fixed list of options (status, priority)	enum	Enforces valid values at the schema level. Faster than lookup table for small, stable lists.
Dynamic list managed by users	lookup	Points to a Lookup Table entity. Use when end users need to add/edit options without schema changes.
Link to another entity	reference (foreign key)	Creates a navigable relationship. Always index reference fields.
Money	decimal(19,4)	Never use float for money. 4 decimal places handles currency subdivisions and rounding.
Dates	date or datetime	Use date for business dates (invoice date). Use datetime with timezone for timestamps (created_at).
Boolean	boolean	Only when the domain is genuinely binary. Avoid for states that might grow (use enum).
Large structured data	json	Escape hatch for semi-structured data. Use sparingly -- cannot be indexed or validated at schema level.

Validation Rules

Validation rules enforce data integrity beyond type constraints:

• Required fields: Mark fields that must have a value on every record. Distinguish between "required on create" vs "required on submit" (draft records may have nulls).
• Format rules: Regex patterns for emails, phone numbers, tax IDs. Apply at the field level.
• Range rules: Numeric min/max, date ranges (end date >= start date).
• Cross-field rules: "If payment_method = 'credit_card', then card_number is required." Implemented as conditional validation.
• Uniqueness constraints: Natural keys (e.g., employee_number must be unique per tenant). Compound uniqueness (e.g., (company_id, account_code) is unique).
• Referential integrity: Foreign keys must point to existing records. ERP•AI enforces this automatically on reference fields.

Soft Deletes vs Hard Deletes

• Soft delete: Set an is_deleted flag (or deleted_at timestamp) instead of removing the row. The record is hidden from normal queries but remains in the database.
• Hard delete: Physically remove the row.

Default to soft deletes in enterprise systems. Reasons: audit trails, referential integrity (other records may point to the deleted record), regulatory requirements (financial records often cannot be destroyed), and undo capability.

Hard deletes are appropriate only for: temporary/draft records, PII erasure under GDPR (after the soft-deleted record has been archived), and staging/import tables.

When using soft deletes, every query must include a WHERE is_deleted = false filter. ERP•AI's query builder handles this automatically when soft delete is enabled on an entity.

Audit Fields

Every entity in an enterprise system should carry these audit fields, added automatically by ERP•AI:

Field	Type	Purpose
created_at	datetime	When the record was first created. Immutable.
created_by	reference(User)	Who created the record. Immutable.
modified_at	datetime	Last modification timestamp. Updated on every save.
modified_by	reference(User)	Who last modified the record.
deleted_at	datetime (nullable)	When the record was soft-deleted. Null if active.
deleted_by	reference(User) (nullable)	Who soft-deleted the record.
version	integer	Optimistic concurrency control. Incremented on every update.

These fields enable audit logging, change tracking, conflict detection, and regulatory compliance.

Multi-Tenancy Patterns

Enterprise SaaS applications must isolate data between tenants (companies, organizations).

Pattern	How It Works	When to Use
Shared database, shared schema	Every table has a tenant_id column. All queries filter by tenant.	Default for ERP•AI. Lowest cost. Works for most SaaS apps.
Shared database, separate schemas	Each tenant gets its own database schema (namespace).	When tenants need different custom fields or entity structures.
Separate databases	Each tenant gets its own database instance.	Regulated industries (banking, healthcare) or very large tenants.

In the shared-schema model, tenant_id is part of every unique constraint, every index, and every foreign key. ERP•AI injects tenant filtering automatically via row-level security policies.

Polymorphic Associations

A polymorphic association allows a single table to reference multiple parent entity types. Example: a Comment entity that can belong to a Task, a SalesOrder, or a SupportTicket.

Implementation options:

• Polymorphic columns: Store commentable_type (string) + commentable_id (integer). Simple but cannot enforce referential integrity at the database level.
• Junction tables: Create task_comments, order_comments, ticket_comments junction tables. Verbose but fully enforces integrity.
• Shared parent entity: Create an abstract Commentable entity that Task, SalesOrder, and SupportTicket extend. The Comment table references Commentable.

Deep Dive -- Inheritance Strategies:

Strategy	Schema Design	Pros	Cons	When to Use
Single-Table Inheritance (STI)	One table holds all subtypes. A type discriminator column identifies the subtype. Subtype-specific columns are nullable for other subtypes.	Simplest queries -- no joins needed. Fast reads. Easy to add new subtypes (add a column).	Table becomes wide and sparse. NULL columns waste space and confuse developers. Validation rules differ per subtype but live on one table.	Few subtypes (< 5) with mostly shared fields and few subtype-specific fields.
Class-Table Inheritance (CTI)	One base table for shared fields, one table per subtype for subtype-specific fields. Subtypes join to the base table via shared PK.	Clean schema -- no NULLs. Each table has only relevant columns. Strong type safety.	Queries require JOINs between base and subtype tables. Inserting a record requires two inserts (base + subtype). Polymorphic queries across subtypes need UNION or LEFT JOIN to all subtype tables.	Many subtypes with significantly different fields. Subtypes have independent lifecycles.
Shared FK (Concrete Table)	No base table. Each subtype has its own table with all fields (shared + specific). No inheritance link at the database level.	No joins. Each table is self-contained. Best write performance.	Shared fields are duplicated across tables. Polymorphic queries require UNION ALL across all subtype tables. Schema changes to shared fields must be applied to every table.	Subtypes are queried independently, rarely together. Performance is paramount. Polymorphic queries are rare.

Performance Implications: STI wins on read performance for polymorphic queries (one table scan, no joins) but pays a storage cost for sparse columns. CTI is balanced -- clean schema, moderate query cost. Concrete table wins on write performance and isolated queries but is worst for polymorphic access. In ERP•AI, prefer CTI (shared parent entity) for core domain models where polymorphic behavior matters, and STI for lightweight type hierarchies where query simplicity is paramount.

In ERP•AI, prefer the shared parent entity pattern (CTI) when the polymorphic behavior is a core design element. Use junction tables for lighter-weight associations. Avoid polymorphic columns unless flexibility outweighs integrity -- they make joins harder and prevent foreign key constraints.

Temporal Modeling

Business data is not static -- it changes over time, and enterprise systems must track those changes with precision. Temporal modeling governs how you represent, store, and query data that has a time dimension.

Slowly Changing Dimensions (SCD)

Borrowed from data warehousing but essential in transactional systems whenever reference data evolves:

SCD Type	Strategy	How It Works	When to Use
Type 1	Overwrite	Replace the old value with the new value. No history preserved.	When history does not matter: fixing a typo in a customer name, updating a phone number.
Type 2	Add row	Insert a new row with the new value. The old row is marked with an effective_end_date and an is_current flag. Each row represents a version of the entity.	When you need full history: employee job title changes, product price changes, customer credit limit adjustments. The most common SCD type in enterprise systems.
Type 3	Add column	Add a previous_value column alongside the current value. Only tracks one prior version.	Rarely useful -- only when you need exactly one level of "before and after" (e.g., previous_address during a recent relocation).
Type 6	Hybrid (1+2+3)	Combines Type 2 rows with Type 3 columns on the current row. The current row carries both the current value and the previous value, while history rows preserve the full trail.	When queries frequently need both the current value and the most recent prior value without scanning history rows.

Effective Dating Patterns

Effective dating attaches a validity period to each record or field value:

• Record-level effective dating: The record carries effective_start_date and effective_end_date. A query "as of" a given date filters for records where effective_start_date <= target_date AND (effective_end_date IS NULL OR effective_end_date > target_date). Used for: employee assignments, price lists, tax rates, exchange rates.
• Field-level effective dating: Individual fields have their own validity periods. More granular but significantly more complex -- typically implemented with a child table holding (entity_id, field_name, value, effective_start, effective_end). Only use for fields that change on independent schedules (an employee's salary changes quarterly but their department changes annually).
• Contiguous dating: Ensure no gaps or overlaps in effective date ranges. When a new rate starts on 2025-01-01, the prior rate must end on 2024-12-31. Enforce this with validation rules or database constraints. Gaps cause "no rate found" errors; overlaps cause ambiguous results.

Bitemporal Data

Bitemporal modeling tracks two independent time axes:

• Valid time (business time): When the fact is true in the real world. "This employee's salary is $120K effective 2025-01-01."
• Transaction time (system time): When the fact was recorded in the system. "We recorded this salary change on 2024-12-15 at 14:23:07."

Why both matter: On December 20, you record a salary of $120K effective January 1. On January 5, you discover the salary should have been $125K and correct it. Without bitemporal tracking, you lose the fact that the system briefly believed the salary was $120K. In regulated industries (banking, insurance, pharmaceuticals), auditors need to reconstruct what the system knew at any point in time -- not just what was true in the real world.

Bitemporal queries:

• AS OF valid time: "What was the employee's salary on March 1?" Filters on valid time.
• AS OF transaction time: "What did the system show as the employee's salary when we ran payroll on January 2?" Filters on transaction time -- returns $120K even though the current record shows $125K.
• AS OF both: "What salary did the system believe was effective on March 1, as of the January 2 payroll run?" Filters on both axes simultaneously.

In ERP•AI, bitemporal support is available on entities where audit reconstruction is required. Enable it in the Entity Builder by selecting "Bitemporal tracking." The platform automatically manages transaction time; builders configure valid time fields.

Schema Versioning

Production schemas evolve continuously. Adding fields, changing types, restructuring relationships -- all while the application is live and serving requests. Schema versioning governs how to make these changes safely.

Forward-Compatible Changes (safe to apply without coordinating consumers):

• Adding a new nullable column. Existing queries ignore it; new queries can use it.
• Adding a new entity (table). No existing code references it.
• Adding a new index. Improves query performance; no code change required.
• Adding a new enum value to the end of an existing enum. Existing code that does not handle the new value falls through to a default case.

Backward-Compatible Changes (existing code continues to work after the change):

• Renaming a column -- only if you create a view or alias with the old name during the transition period.
• Widening a column type (e.g., VARCHAR(50) to VARCHAR(100), INT to BIGINT).
• Making a required column nullable (relaxing a constraint).

Breaking Changes (require coordinated code and schema updates):

• Removing a column. Any code referencing it will fail.
• Renaming a column without an alias.
• Changing a column type to something incompatible (e.g., string to integer).
• Making a nullable column required (tightening a constraint). Existing records with nulls will violate the constraint.
• Splitting or merging entities.

Migration Strategies:

• Expand-and-contract: The safest pattern for zero-downtime evolution. Phase 1 (expand): add the new column/table alongside the old one; update write paths to populate both. Phase 2 (migrate): backfill the new column from old data. Phase 3 (contract): remove the old column once all read paths have switched to the new one. Each phase is a separate deployment.
• Dual-write transition: During the transition window, the application writes to both old and new structures. Read paths gradually shift from old to new. Validated by comparing outputs from both paths.
• Schema version registry: Maintain a schema_versions table that records every migration applied, its timestamp, and a checksum. Tools like Flyway, Liquibase, or ERP•AI's built-in migration runner use this to ensure migrations are applied exactly once and in order.
• Zero-downtime deployment: Never run a migration that locks a table for more than a few seconds in production. Large backfills should run as background jobs. Use CREATE INDEX CONCURRENTLY (Postgres) or equivalent non-blocking operations. Test migration duration against production-sized data before deploying.

Data Lineage

Data lineage tracks where data comes from, how it transforms, and where it flows. In enterprise systems with hundreds of entities and dozens of integrations, lineage is essential for impact analysis, compliance, and debugging.

Upstream Dependency Tracking: For every field in your schema, document the data source. A customer_credit_score field might originate from an external credit bureau feed, pass through a transformation rule, and land in the Customer entity. When the credit bureau changes their API, lineage tells you exactly which fields and entities are affected.

Impact Analysis for Schema Changes: Before dropping or renaming a column, lineage answers: "What reports, integrations, workflows, and calculated fields depend on this column?" Without lineage, schema changes are guesswork that routinely breaks downstream consumers.

Column-Level Lineage: Entity-level lineage ("the Order entity feeds the Revenue report") is useful but insufficient. Column-level lineage tracks that order.total_amount feeds revenue_fact.gross_revenue via a SUM aggregation in the nightly ETL. This granularity is required for GDPR (which fields contain PII and where does that PII flow?) and SOX compliance (which fields feed financial reports?).

Implementation in ERP•AI: The platform's Entity Builder and Integration Mapper automatically capture lineage metadata. Custom transformations should register their lineage via the Lineage API. The Lineage Explorer visualizes the full dependency graph for any entity or field, enabling "what breaks if I change this?" analysis before any schema modification.

Graph and Network Models

Self-referencing hierarchies are pervasive in enterprise data: org charts, charts of accounts, product categories, bill of materials, approval chains, reporting structures. The choice of hierarchy representation has dramatic performance implications.

Pattern	How It Works	Insert/Move	Query Subtree	Query Depth	Space	Best For
Adjacency List	Each row has a parent_id FK pointing to its parent row in the same table.	O(1) -- update one row	Requires recursive CTE or application-level recursion. Slow for deep trees.	Requires recursion.	O(n) -- one FK per row	Shallow hierarchies (< 5 levels), frequent inserts/moves, trees where subtree queries are rare.
Nested Set	Each row stores lft and rgt values representing its position in a depth-first traversal. A node's descendants have lft > parent's lft AND rgt < parent's rgt.	O(n) -- must renumber all nodes to the right of the insertion point	O(1) -- single range query on lft/rgt	Stored implicitly in the nesting	O(n) -- two integers per row	Read-heavy hierarchies that rarely change (chart of accounts, product categories).
Materialized Path	Each row stores the full path from root as a string: /1/4/17/.	O(1) -- concatenate parent's path + own ID	LIKE '/1/4/%' -- fast with a prefix index	Computed from path string length / delimiter count	O(n * d) where d is depth	Hierarchies displayed as breadcrumbs, URL-like paths, moderate depth.
Closure Table	A separate table stores every ancestor-descendant pair: (ancestor_id, descendant_id, depth). A node with 3 ancestors has 3 rows (plus a self-referencing row).	O(d) -- insert one row per ancestor of the new node	O(k) -- query all rows where ancestor_id = target, returns all descendants	Stored in the depth column	O(n * d) -- can be large for deep, wide trees	Complex hierarchy queries (all ancestors, all descendants, nodes at depth N), frequent reads and moderate writes.

Performance Comparison for 10,000-Node Tree (5 Levels Deep):

Operation	Adjacency List	Nested Set	Materialized Path	Closure Table
Get children of node	~1ms (indexed FK)	~1ms (range query)	~2ms (LIKE with prefix index)	~1ms (indexed ancestor_id, depth=1)
Get entire subtree	~50-200ms (recursive CTE)	~1ms (range query)	~5ms (LIKE prefix)	~2ms (indexed ancestor_id)
Get all ancestors to root	~5ms (recursive CTE, d iterations)	~2ms (range query reversed)	~0ms (parse the path string)	~1ms (indexed descendant_id)
Move subtree to new parent	~1ms (update one FK)	~100-500ms (renumber affected nodes)	~10-50ms (update paths for all descendants)	~10-50ms (delete and reinsert closure rows)
Insert new leaf node	~1ms	~50-200ms (renumber)	~1ms	~1ms per level of depth

Recommendation in ERP•AI: Use adjacency list as the default (it is the simplest and handles most enterprise hierarchy needs). Add a closure table alongside the adjacency list when the application requires frequent subtree queries (e.g., "total revenue for this division and all sub-divisions," "all direct and indirect reports of this manager"). The closure table can be maintained via triggers or application logic when the adjacency list changes. Use materialized path when you need to display breadcrumbs or path strings in the UI. Avoid nested set unless the hierarchy is nearly static -- the renumbering cost on writes is prohibitive for dynamic trees.

Flexibility vs Structure: EAV and JSON Columns

The tension between strict schemas (fixed columns, strong types) and flexible schemas (user-defined fields, dynamic attributes) is central to enterprise app design.

• Entity-Attribute-Value (EAV): A meta-table with columns (entity_id, attribute_name, attribute_value). Infinitely flexible. Terrible for queries, reporting, indexing, and validation. Avoid in ERP•AI unless building a truly generic platform-of-platforms.
• JSON columns: Store a JSON blob for custom/overflow attributes. Better than EAV (data stays on the row), but still hard to index, validate, or report on.
• Custom field registry: ERP•AI's recommended approach. Builders define custom fields through the platform, which dynamically adds typed columns to the underlying table. This preserves schema-level type safety while allowing per-tenant customization.

Rule of thumb: Use strict schema for any field that appears in queries, reports, integrations, or business rules. Reserve JSON/custom fields for truly ad-hoc, user-managed data.

Workflow

1. Gather Requirements

• Interview business stakeholders to identify the entities they work with daily.
• Collect sample documents: invoices, purchase orders, employee records, reports.
• Identify the master data (slowly changing reference data) vs transactional data (high-volume event records).
• Tool: ERP•AI's Requirements Workspace for capturing entity lists and business rules.
• Watch out for: Stakeholders describing reports as if they are entities. A "Sales Summary" is a view, not a table.
• Output: Entity inventory with preliminary field lists.

2. Design the Conceptual Model

• Draw an Entity-Relationship Diagram (ERD) with entities, attributes, and relationships.
• Identify cardinalities (1:1, 1:N, M:N).
• Mark mandatory vs optional relationships.
• Tool: ERP•AI's Visual Schema Designer or an external tool (draw.io, Lucidchart) for initial sketching.
• Watch out for: Circular dependencies (A references B references C references A). These create insert-order problems. Break cycles with nullable foreign keys or deferred constraints.
• Output: Conceptual ERD document.

3. Define the Logical Model

• Normalize to 3NF for transactional entities.
• Select field types using the Field Type Selection table above.
• Define primary keys (prefer surrogate auto-increment IDs; preserve natural keys as unique indexed columns).
• Add audit fields.
• Define validation rules.
• Configure soft delete behavior.
• Tool: ERP•AI's Entity Builder -- define each entity, its fields, types, constraints, and relationships.
• Watch out for: Over-normalization. If a lookup table has only id and name and never changes, consider using an enum instead.
• Output: Fully specified logical schema in ERP•AI.

4. Handle Multi-Tenancy and Security

• Add tenant_id to all entities (ERP•AI does this automatically in shared-schema mode).
• Define which fields are sensitive (PII, financial) for field-level encryption.
• Map entities to security roles: who can Create, Read, Update, Delete each entity.
• Tool: ERP•AI's Security Configuration panel.
• Watch out for: Forgetting to apply tenant filters to M:N junction tables. Data leaks happen at the joins.
• Output: Security annotation on each entity.

5. Design for Analytics

• Identify key metrics and KPIs the business needs.
• Build star-schema views on top of the transactional model: fact tables for measures, dimension tables for slicing.
• Create materialized views or scheduled ETL for performance.
• Tool: ERP•AI's Analytics Designer.
• Watch out for: Putting aggregation logic in the transactional schema. Keep OLTP and OLAP concerns separate.
• Output: Analytics schema or view definitions.

6. Validate and Iterate

• Generate sample data and test: Can all business scenarios be represented?
• Test edge cases: What happens when a Customer is deleted but has open Orders? What if a Product belongs to zero Categories?
• Run the schema through integration review: Can external systems map to these entities?
• Tool: ERP•AI's Data Simulator and Integration Previewer.
• Watch out for: Designing only for current requirements. Leave room for known future needs without over-engineering.
• Output: Validated, tested schema ready for development.

Decision Guide

When to Normalize vs Denormalize

Situation	Recommendation
Transactional data (orders, invoices, journal entries)	Normalize to 3NF. Integrity matters most.
Reporting/analytics views	Denormalize into star schema. Query speed matters most.
Frequently joined lookup data (country names, currency codes)	Keep normalized but cache aggressively.
Audit/history tables	Denormalize (snapshot the state at time of event).
High-write-volume event logs	Append-only, minimal normalization. Summarize in batch.

Enum vs Lookup Table

Factor	Use Enum	Use Lookup Table
Number of options	< 15 and rarely changes	> 15 or changes frequently
Who manages options	Developers/builders	End users / admins
Used in code logic	Yes (switch statements, conditionals)	No (display only)
Multi-language support needed	No	Yes (lookup rows can have translations)
Options have extra attributes (code, sort order, parent)	No	Yes

Surrogate Key vs Natural Key

Factor	Surrogate Key (auto-increment ID)	Natural Key (e.g., employee_number)
Stability	Never changes	May change (reorgs, mergers)
Simplicity	Always an integer, easy to join	May be composite, harder to join
Business meaning	None -- opaque identifier	Self-describing
Recommendation	Use as primary key	Add as a unique indexed column alongside the surrogate key

Common Patterns

Chart of Accounts (Hierarchical Entity)

A General Ledger Chart of Accounts is a self-referencing hierarchy:

• Entity: Account
• Fields: account_code (string, unique per tenant), account_name, account_type (enum: Asset, Liability, Equity, Revenue, Expense), parent_account_id (self-reference, nullable), is_header (boolean -- header accounts group children but hold no balances), is_active (boolean).
• Pattern: Adjacency list (self-referencing FK) for the tree structure. Add level and full_path computed fields for query convenience. If deep nesting or subtree queries are common, also consider a closure table.

Customer Hierarchy (Multi-Level Grouping)

Enterprise customers often have a hierarchy: Global HQ > Regional Entity > Local Branch > Department.

• Entity chain: CustomerGroup (top level) -> Customer (billing entity) -> CustomerSite (ship-to location) -> CustomerContact (person).
• The Customer holds the financial relationship (credit limit, payment terms). The CustomerSite holds the address. The CustomerContact holds the person.
• Anti-pattern: Flattening everything into a single Customer table with address_1, address_2, contact_name_1 columns. This breaks as soon as a customer has 3+ sites or contacts.

Product Catalog (Variant Pattern)

Products with variants (size, color, configuration):

• Entity chain: ProductFamily -> Product (the sellable SKU) -> ProductVariant (specific size/color).
• ProductFamily holds shared attributes (brand, category, description). Product holds pricing and inventory. ProductVariant holds the differentiating attributes.
• For configurable products (e.g., a server with CPU/RAM/storage options), use a ProductOption entity with a M:N relationship to Product.

Anti-Patterns to Avoid

• God Table: One table with 200+ columns covering multiple business concepts. Split into focused entities.
• Stringly Typed Data: Storing dates as strings, numbers as strings, booleans as "Y"/"N" strings. Use proper types.
• Copy-Paste Denormalization: Duplicating customer name on every order line "for convenience." Use joins.
• Premature JSON: Throwing structured data into a JSON column because "we might need flexibility." Define the schema first; add JSON escape hatches only for genuinely dynamic data.
• Missing Indexes: Every foreign key, every field used in WHERE clauses, and every field used in ORDER BY should have an index. ERP•AI auto-indexes reference fields, but custom query patterns may need manual indexes.
• Ignoring Temporal Data: Business data changes over time. A product price today is not the same as the price when the order was placed. Snapshot temporal values (order line captures unit_price_at_time_of_order) or use history tables.
• God Entity: A single entity that accumulates every attribute even tangentially related to a business concept. The Customer entity should not hold shipping preferences, support case counts, marketing segment scores, billing configuration, and user authentication data in 200+ columns. Split into focused entities: Customer, CustomerShippingProfile, CustomerBillingConfig, CustomerMarketingProfile. A God Entity is the data model equivalent of a God Object -- it violates single responsibility and makes every change risky.
• Phantom Foreign Keys (App-Level Only): Storing a reference to another entity's ID without a database-level foreign key constraint -- relying on application code to enforce the relationship. This works until it does not: a bug, a manual database edit, or a migration script bypasses the application and creates orphaned references. Always enforce referential integrity at the database level. If the relationship is cross-database or cross-service, document it explicitly and implement validation checks in integration tests.

Checklist

• All business entities identified and documented
• Entity-Relationship Diagram created and reviewed with stakeholders
• Transactional entities normalized to 3NF
• Field types chosen deliberately (no stringly typed data)
• Validation rules defined for all critical fields
• Primary keys are surrogate; natural keys preserved as unique indexes
• All reference (FK) fields indexed
• Audit fields present on all entities (created_at/by, modified_at/by)
• Soft delete configured where required (financial, regulated data)
• Multi-tenancy isolation verified (tenant_id on all tables, junction tables included)
• Sensitive fields marked for encryption
• Security roles mapped to entities (CRUD permissions)
• Analytics views designed (star schema for dashboards/reports)
• Edge cases tested (deletes with dependencies, empty relationships, max cardinality)
• Schema reviewed against integration requirements (field names, types, granularity)
• Temporal data handled (snapshots for prices, rates, statuses at point-in-time)
• Temporal modeling pattern selected (SCD Type 2, effective dating, bitemporal) based on audit requirements
• Schema versioning strategy defined (expand-and-contract for breaking changes)
• Data lineage captured for fields that feed reports, integrations, or compliance workflows
• Hierarchy pattern selected (adjacency list, closure table, etc.) with performance implications understood
• Polymorphic associations use enforced referential integrity (no phantom foreign keys)

ERP•AI & Proto

ERP•AI: Entity designer supports all core field types (string, decimal, reference, enum, lookup) with built-in validation, indexing, and multi-tenancy isolation. Schema changes flow through the config promotion pipeline with backward-compatibility checks.

Proto: During data architecture missions, Proto loads schema patterns and normalization decision guides into L2 session memory. It reasons over entity relationships and field type choices in the REASON phase, then generates schema definitions in the ACT phase -- iterating as validation rules or integration requirements surface new constraints.

Related

• Data Migration -- migrating data into the schemas you design here
• Security & Roles -- controlling who can access which entities and fields
• Workflow Automation -- building processes that operate on your data model
• Integrations -- exposing your data model to external systems