# Schema Design

> Schema design best practices — entity modeling, relation patterns, creation order, formulas, views. This skill should be used when the user asks to design a database schema, plan tables and relationships, or build CRM/ERP/project databases.

- Skill: `agents-store/schema-design` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add agents-store/schema-design`
- Raw SKILL.md: https://api.skillmd.com/api/skills/agents-store/schema-design/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: agents-store (https://skillmd.com/u/agents-store)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/agents-store/schema-design

---


# NocoDB Schema Design

Expert guidance on designing and building complex NocoDB schemas — data modeling, relations, rollups, formulas, views, and forms.

## Design Principles

Before creating any table:
1. **Identify entities and relationships** — think ERD (Entity Relationship Diagram)
2. **Define primary display field** for each table — set BEFORE creating relations
3. **Plan Lookup and Rollup fields** in advance — they depend on relations existing first
4. **Design views** per user role or workflow

## Creation Order (Critical)

Dependencies must exist before dependent fields. Follow this strict order:

```
Step 1: Reference tables (no dependencies)
  └── Statuses, Categories, Tags, Priorities
  └── Use SingleSelect columns for fixed option lists

Step 2: Main entity tables
  └── Contacts, Companies, Deals, Products, Tasks
  └── Include basic fields: text, number, date, select

Step 3: Relations (LinkToAnotherRecord)
  └── Both tables must exist before linking
  └── Always use table_id, never table_name
  └── Types: hm (has many), mm (many to many)

Step 4: Lookup fields
  └── Relation column must exist first
  └── Shows a specific field from the related table
  └── Example: show Contact.Email inside a Deal record

Step 5: Rollup fields
  └── Relation column must exist first
  └── Aggregates: sum, count, avg, min, max
  └── Example: SUM of Deal.Amount in Contact record

Step 6: Formula fields
  └── All referenced fields must exist
  └── Calculations, conditionals, string manipulation

Step 7: Views
  └── All fields should be in place
  └── Grid, Kanban, Gallery, Form, Calendar
  └── Configure filters, sorts, field visibility per view
```

## Workflow: Build a Schema

```
1. list_tables() -> Check what already exists
2. create_table(reference_tables) -> Statuses, Categories first
3. create_table(main_tables) -> Contacts, Deals, etc.
4. create_column(type=LinkToAnotherRecord) -> Relations between tables
5. create_column(type=Lookup) -> Show fields from related tables
6. create_column(type=Rollup) -> Aggregate related data
7. create_column(type=Formula) -> Calculated fields
8. create_view(type=Grid/Kanban/Form) -> Views for each workflow
9. list_tables() + list_columns() -> Verify structure
```

## Relation Patterns

### One-to-Many (hm)
One parent, many children. Create Link in the child table.

```
Example: Company has many Contacts
Tool: create_column
Input: {
  "table_id": "<contacts_table_id>",
  "name": "Company",
  "type": "LinkToAnotherRecord",
  "linked_table_id": "<companies_table_id>"
}
```

### Many-to-Many (mm)
Both sides have many. NocoDB auto-creates a junction table.

```
Example: Deals have many Products, Products belong to many Deals
Tool: create_column
Input: {
  "table_id": "<deals_table_id>",
  "name": "Products",
  "type": "LinkToAnotherRecord",
  "linked_table_id": "<products_table_id>",
  "relation_type": "mm"
}
```

### Lookup + Rollup After Relation
```
After creating Deal → Contact relation:

Lookup — show Contact.Email in Deal:
create_column(table_id=deals, type=Lookup, relation_column_id=<link_col>, lookup_column_id=<email_col>)

Rollup — show SUM of Deal.Amount in Contact:
create_column(table_id=contacts, type=Rollup, relation_column_id=<link_col>, rollup_column_id=<amount_col>, rollup_function="sum")
```

## Formula Patterns

| Pattern | Formula | Use Case |
|---------|---------|----------|
| Conditional | `IF({Amount} > 10000, "Enterprise", "SMB")` | Categorize records |
| Display name | `CONCAT({First Name}, " ", {Last Name})` | Combined text |
| Due date | `DATEADD({Created}, 30, 'days')` | Auto deadline |
| Days elapsed | `DATETIME_DIFF(NOW(), {Created}, 'days')` | Age tracking |
| Progress | `IF({Status} = "Done", 1, 0)` | Completion flag |
| Percentage | `ROUND({Completed} / {Total} * 100, 1)` | Progress % |

## View Recommendations

| View Type | Best For | Group By |
|-----------|----------|----------|
| Grid | General data work, filtering | — |
| Kanban | Pipeline stages, status tracking | SingleSelect (Status/Stage) |
| Gallery | Visual items with images | Cover image field |
| Form | Data collection, lead capture | — |
| Calendar | Date-based items, scheduling | Date/DateTime field |

## Common Design Mistakes

1. **Everything in one table** — normalize into separate entities with relations
2. **Duplicating data** instead of relations — use Links + Lookups instead of copying data
3. **Creating formulas before relations** — relations must exist first for Lookup/Rollup
4. **Forgetting primary display** — set before creating relations (affects how linked records display)
5. **Using table names in API calls** — always resolve to table_id via list_tables first
6. **No reference tables** — use separate tables or SingleSelect for statuses/categories instead of free text

## Best Practices

1. **Name tables as plural nouns** — Contacts, Deals, Products (not Contact, Deal)
2. **Use Title/Name as first field** — makes primary display intuitive
3. **Add Created/Updated dates** — track record lifecycle
4. **Use SingleSelect over free text** for statuses — enables Kanban views and consistent filtering
5. **Plan relations on paper first** — draw entity relationships before building
6. **Test with sample data** — create a few records after schema to verify relations work