Data Model

Oracle Data Model Design Standards

This skill covers table design, column conventions, constraints, indexes, and data integrity patterns for Oracle databases. It complements the plsql-code-quality skill (which covers naming conventions for all database objects) by focusing specifically on the structural design of the data layer.

A well-designed data model is the most valuable investment in any application. Poor design will haunt you for the lifetime of the project — every workaround, every denormalization shortcut, every unnamed constraint accumulates into technical debt that becomes harder to fix as data grows.

Stamp

On success, run: python3 /Users/dobby/Library/CloudStorage/Dropbox/BRAIN/AI/SCRIPTS/skills_log.py stamp data-model

Design Principles

Spend time getting the model right. Changing a data model after it has data and dependencies is orders of magnitude more expensive than getting it right upfront. Before writing any DDL, sketch the entities, relationships, and cardinalities. Validate them against real use cases.

Normalize first, denormalize with intent. Start with a properly normalized model (at least 3NF). Denormalize only when you have measured performance evidence that normalization is the bottleneck — not because you assume it will be. Document every denormalization with a comment explaining why it exists.

Visualize your model. Use Oracle SQL Developer Data Modeler or a similar tool to maintain an ERD for each application module. The visual representation catches design issues that are invisible when staring at DDL scripts. Keep the diagrams in version control alongside the schema.

Table Design

Naming

Use natural, descriptive names in plural form and snake_case: orders, order_items, customer_addresses. Plural avoids keyword conflicts (user is reserved, users is not) and reads naturally in queries (SELECT ... FROM orders).

For multi-application schemas, prefix tables with a short (2–4 character) application code: shop_orders, shop_customers. This groups related objects in the data dictionary and makes ownership clear when multiple applications share a schema.

Audit Columns

Every table should include standard audit columns:

created_by      VARCHAR2(128)   DEFAULT core.get_user() NOT NULL,
created_at      DATE            DEFAULT SYSDATE         NOT NULL,
updated_by      VARCHAR2(128),
updated_at      DATE

Populate updated_by and updated_at via a compound trigger or the application layer. Do not create foreign keys on audit columns — the referenced user may be deleted or renamed, and audit columns are informational, not relational.

Primary Keys

Every table must have a primary key. Prefer surrogate keys (numeric IDs) generated via identity columns or sequences:

CREATE TABLE orders (
    order_id        NUMBER GENERATED ALWAYS AS IDENTITY,
    ...
    CONSTRAINT orders_pk PRIMARY KEY (order_id)
);

Use GENERATED ALWAYS AS IDENTITY when the ID is purely internal. Use GENERATED BY DEFAULT AS IDENTITY when external systems may need to supply specific values (e.g. data migration).

Use explicit sequences only when you need cross-table sharing, pre-fetching, or specific caching behavior. Name them to match the column: order_id_seq.

Natural Keys as Unique Constraints

When a table has a natural key (e.g. email, order_number), enforce it with a unique constraint. The surrogate PK handles relationships; the unique constraint enforces business uniqueness.

Column Design

Naming

Include the entity context in column names. Avoid bare generic names like id, name, status, type — they become ambiguous the moment you join two tables.

-- Avoid
id, name, status, type, desc

-- Preferred
order_id, customer_name, order_status, item_type, project_desc

Foreign key columns should match the referenced primary key: orders.customer_id references customers.customer_id. This makes joins self-documenting.

Data Types

Be consistent with data types across the schema. If customer_id is NUMBER in one table, it must be NUMBER everywhere. Common conventions:

| Purpose | Type | Notes | | -------------- | ----------------------------------------- | ----------------------------------------------- | | Surrogate keys | NUMBER | Or NUMBER GENERATED AS IDENTITY | | Short text | VARCHAR2(n) | Use realistic lengths, not 4000 everywhere | | Long text | CLOB | For unbounded text | | Dates | DATE | Unless sub-second precision needed | | Timestamps | TIMESTAMP or TIMESTAMP WITH TIME ZONE | When sub-second or timezone matters | | Booleans | VARCHAR2(1) or NUMBER(1) | 'Y'/'N' or 1/0 — pick one and be consistent | | Money/amounts | NUMBER(p, s) | Always specify precision and scale |

Avoid using CHAR — it pads with spaces and causes subtle comparison bugs. Use VARCHAR2 for everything string-based.

NOT NULL Constraints

Apply NOT NULL aggressively. Every column that must have a value should be constrained. A missing NOT NULL is a bug waiting to happen — the application might enforce it today, but a future data migration or API caller will not.

Name your NOT NULL constraints: orders_customer_id_nn. When a constraint violation fires, the name tells you exactly which column is the problem.

Constraints

Naming Convention

Name every constraint explicitly. System-generated names like SYS_C007234 are useless in error messages and impossible to reference in code.

| Type | Convention | Example | | ----------- | ------------------------ | ----------------------- | | Primary Key | {table}_pk | orders_pk | | Unique | {table}_{column(s)}_uq | users_email_uq | | Foreign Key | {table}_{column}_fk | orders_customer_id_fk | | Check | {table}_{column}_ck | orders_status_ck | | Not Null | {table}_{column}_nn | orders_customer_id_nn |

Foreign Keys

Create foreign keys for every relationship. They enforce referential integrity, document relationships, and enable the optimizer to make better join decisions.

Set the ON DELETE clause intentionally:

• ON DELETE CASCADE — when child rows have no meaning without the parent (e.g. order items when the order is deleted).
• ON DELETE SET NULL — when the child can exist independently but loses its association.
• No clause (default: restrict) — when deleting a parent with children is a data integrity violation that should be prevented.

Check Constraints

Use check constraints for columns with a fixed set of valid values:

CONSTRAINT orders_status_ck CHECK (order_status IN ('DRAFT', 'SUBMITTED', 'APPROVED', 'SHIPPED', 'CANCELLED'))

This is enforced at the database level regardless of which application layer writes the data.

Indexes

Naming

Use {table}_{column(s)}_ix followed by a number if there are multiple indexes on the same column combination: orders_customer_id_ix, orders_created_at_ix.

When to Create Indexes

• Foreign key columns — always. Oracle does not automatically index FK columns, and without an index, any DML on the parent table locks the child table.
• Columns frequently used in WHERE clauses — when the query accesses a small percentage of rows.
• Columns used in ORDER BY or GROUP BY — when the sort is expensive.
• Unique constraints — Oracle creates these automatically; no manual action needed.

When NOT to Create Indexes

• On small tables (< 1000 rows) — full table scan is faster.
• On columns with very low cardinality (e.g. a boolean flag with two values) — unless combined with other columns in a composite index.
• Speculatively — every index slows down DML. Add indexes based on measured query performance, not assumptions.

Schema Change Practices

Repeatable DDL

All DDL scripts must be repeatable — running them multiple times must not cause errors. Use patterns like:

-- For tables: check existence before creating
BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE ...';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -955 THEN RAISE; END IF;  -- ORA-00955: name already used
END;
/

-- For columns: check existence before adding
-- For constraints: drop and recreate, or check existence
-- For views, packages, triggers: CREATE OR REPLACE

Additive Changes First

Prefer additive changes (add column, add constraint) over destructive ones (drop column, rename). When destructive changes are necessary, plan the migration: add the new structure, migrate the data, update all consumers, then remove the old structure.

Document Structural Decisions

When a design choice is non-obvious — a denormalized column, an unusual constraint, a missing foreign key — add a comment on the table or column explaining why:

COMMENT ON COLUMN orders.cached_total IS 'Denormalized from order_items for dashboard performance. Updated by orders_trg on item changes.';

This saves the next developer from "fixing" what looks like a mistake but is actually an intentional decision.

Examples

Design a new set of tables for an orders module, applying naming and constraint conventions:

/data-model

Review an existing schema for missing foreign-key indexes and unnamed constraints:

/data-model

Plan an additive schema change with repeatable DDL before writing the migration:

/data-model

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: jkvetina
• Source: jkvetina/AI_SKILLS
• License: MIT

Install and usage instructions live in the source repository linked above.