--- name: airtable-base-schema-design description: Plan and build a new table or field structure in an Airtable base for a use case such as a tracker, CRM or pipeline, reviewing the existing schema first and proposing every table and field change for approval before creating anything. Use when the user asks to design, set up, extend, restructure or model data in Airtable, or says "create a tracker", "add a table for", "set up a CRM in my base" or "what fields should I have". --- # Design an Airtable table structure The goal is a schema that a person can fill in correctly six months from now. Most Airtable pain comes from structure decided in a hurry: one table holding several entities, text fields where a select belongs, duplicated values that drift apart. Decide the structure on paper, get a yes, then build. ## 1. Confirm the target before touching anything Use `list_bases` or `search_bases` to identify the base by name, then `list_tables_for_base` to see what already exists. Confirm with the user which base is the target. Do not assume the most recently used one. If the user is unsure, stop and ask. Creating tables in the wrong base is hard to undo cleanly. If the base already has a table that covers the use case, say so and propose extending it rather than creating a parallel one. Two trackers for the same thing is the most common result of skipping this check. ## 2. Read the existing schema of related tables For each table that the new design will touch or link to, call `get_table_schema`. Record field names, field types and, where the schema shows them, select options and link targets. You are looking for: - **Existing fields that already hold the data** (a `Status` or `Owner` field already exists under another name). Reuse them. - **Naming conventions** in use (Title Case, prefixes, singular or plural table names). Match them. - **Link fields** already pointing at tables you would use. A new link field to the same table is often a duplicate. - **Lookup and formula fields** that depend on fields you may rename. Renaming does not warn about dependants. Note them as a risk. Do not sample records to infer schema unless the schema is ambiguous. `list_records_for_table` returns pages, so use a small page size and say how much you looked at. ## 3. Model entities first, then fields Write the model in plain language before any field names: 1. **List the entities** (things with their own identity): Projects, Tasks, Customers, Contacts, Deals. A thing that needs its own status, owner or history is an entity. A thing that is just a label is a field. 2. **Decide the relationship** for each pair: one-to-many (a Project has many Tasks), or many-to-many (a Contact works at several Companies, a Task has several Tags). Many-to-many needs a link field on both sides or a junction table; choose deliberately. 3. **Assign fields to exactly one entity.** A customer's address belongs on Customers, not repeated on every Order, unless the address at order time is itself a fact you need to keep. 4. **Choose field types from the data, not from habit:** - Fixed set of values (Status, Priority, Stage) → single select, with the options listed. Free text here means typos that break every view and filter. - Relationship to another entity → link to record, never a text copy of the name. - Amount, count, percent → number or currency, not text. - Date that drives reporting or reminders → date field, not text. - Calculated from other fields (days overdue, total) → formula or rollup, not a value typed by hand. - Yes or no → checkbox. 5. **Name the primary field** as the thing a human would recognise the record by (Name, Title, Company), since it is what appears in every link. ## 4. Check the design against real questions Before proposing, write three questions the user will ask of this data ("which deals closed last quarter by owner", "tasks overdue by project"). For each, trace the path through your tables and fields. If the path needs a text match, a manual count or a field that does not exist yet, the design is missing something. This test catches more problems than any rule above. ## 5. Propose the change set and wait for approval Write the full change set as a list before calling any create tool: - Tables to create (name, purpose, primary field). - Fields to create per table (name, type, options for selects, link target for links). - Existing fields to reuse, rename or leave alone. - Anything you considered and rejected, with the reason. Each `create_table`, `create_field`, `update_table`, `update_field` and `create_records_for_table` call changes the live base that other people may use. Do not run any of them until the user has approved the specific change set. Wait for the user's explicit yes, and if they change anything, re-issue the change set before building. ## 6. Build in dependency order, then verify Create linked-to tables before the tables that link to them. Create fields after their table. Create a new link field only after both tables exist. After each step, call `get_table_schema` on what you changed and compare with the approved list. If a call fails, stop, report what was created so far and wait for direction. Do not retry a partially applied change blindly, since that can create duplicates. If you add sample rows to test a design, use `create_records_for_table` in batches of at most 10 records per request, and only with the user's explicit yes. Tell the user which rows are samples so they can delete them. ## Common patterns and traps - **One table for everything.** A "Tracker" that mixes projects, tasks and people leads to blank cells everywhere. Split by entity once the same column is empty for most rows. - **Status as text.** Typos ("In progress", "in-progress") silently split one status into several. Use a single select. - **Copied values instead of links.** A customer name typed into each order goes stale on the first rename. Link to the Customers table and look up what you need. - **Calculated values typed in by hand.** Totals and "days open" drift. Make them formulas or rollups. - **Renaming a field that a formula or lookup reads.** The dependants may break without a clear error. Check `get_table_schema` for dependants before renaming, and prefer adding a new field over renaming when the old one is in use. - **Changing a field type.** Converting text to a select or number can discard or mangle existing values. Propose it as a migration with a count of what will change, not as a quiet edit. - **Over-normalising.** Five linked tables for a list of 30 items is harder to use than one table with a select. Stop at the level the questions in step 4 need. - **Assuming a permission level.** If a create call is refused, report the permission error and what the user would need to change. Do not try workarounds through other tools. ## Output ``` Target: , Use case: Model - (primary field: ) — - ↔ : Proposed changes (not yet applied) Tables to create: , Fields to create: - . — , , Existing fields reused:
., ... Considered and rejected: