data-modeling

What this skill does

# Glide Data Modeling

## Key Concept: Column-Based Calculations

**IMPORTANT**: All calculations in Glide are built in the Data Editor by adding computed columns that build off one another. Unlike spreadsheets with cell formulas, Glide uses a column-based computation model.

To build complex calculations:
1. Create intermediate computed columns
2. Chain columns together (one column references another)
3. Build up to your final result step by step

Example: Calculate total with tax
1. Column: `Subtotal` (Math: Price * Quantity)
2. Column: `Tax` (Math: Subtotal * 0.08)
3. Column: `Total` (Math: Subtotal + Tax)

## Column Types Overview

### Basic Columns (Data Storage)

| Type | Description | Use Case |
|------|-------------|----------|
| **Text** | String data | Names, descriptions |
| **Number** | Numeric values | Prices, quantities |
| **Boolean** | True/False | Flags, toggles |
| **Image** | Image URL | Profile photos, product images |
| **Date & Time** | Timestamps | Due dates, created at |
| **URL** | Web links | External resources |
| **Row ID** | Unique identifier | **Primary key** - add to every table first |
| **Rich Text** | Formatted text | Long descriptions |
| **Email** | Email addresses | Contact info |
| **Phone Number** | Phone format | Contact info |
| **Duration** | Time duration | Task duration |
| **Emoji** | Emoji picker | Reactions, status |
| **Multiple files** | File array | Attachments |
| **Multiple images** | Image array | Photo galleries |

### Computed Columns (Calculations)

| Type | Description | Use Case |
|------|-------------|----------|
| **Math** | Arithmetic calculations | Totals, percentages |
| **Template** | Text concatenation | Display names, labels |
| **If-Then-Else** | Conditional logic | Status labels, flags |
| **Query** | Query other tables | Cross-table data |
| **Relation** | Link rows between tables | Relationships |
| **Lookup** | Get values via relation | Related data |
| **Rollup** | Aggregate related data | Sums, counts, averages |
| **Single Value** | One value from relation | First/last related item |
| **Joined List** | Array to text | Comma-separated list |
| **Split Text** | Text to array | Parse delimited data |
| **Make Array** | Create array | Multiple values |
| **Distance** | Geographic distance | Location calculations |
| **Generate Image** | Create images | Dynamic graphics |
| **Construct URL** | Build URLs | Dynamic links |

### AI Columns (Powerful!)

Glide AI columns run inference on your data automatically. **Look for opportunities to add these to make apps more useful.**

| Type | Description | Use Case |
|------|-------------|----------|
| **Generate Text** | AI text generation | Summaries, descriptions, responses, recommendations |
| **Image to Text** | Extract info from images | Receipt scanning, document OCR, image analysis |
| **Document to Text** | Extract text from docs | PDF parsing, document summarization |
| **Audio to Text** | Transcribe audio | Voice notes, meeting recordings |
| **Text to Boolean** | AI classification | Sentiment analysis, spam detection, yes/no questions |
| **Text to Choice** | AI categorization | Auto-tagging, priority assignment, category detection |
| **Text to JSON** | Extract structured data | Form parsing, entity extraction |
| **Text to Number** | Extract numbers | Data extraction from text |
| **Text to Date** | Parse dates | Natural language date parsing |
| **Text to Texts** | Split into multiple | List extraction, keyword extraction |

#### AI Column Ideas by App Type

| App Type | AI Enhancement Ideas |
|----------|---------------------|
| **Task Management** | Auto-prioritize tasks, summarize long descriptions, extract due dates from text |
| **Inventory** | Generate product descriptions, extract info from product images |
| **CRM** | Summarize customer notes, sentiment analysis on feedback, auto-categorize leads |
| **Content/Documents** | Summarize documents, extract key points, auto-tag content |
| **Support/Tickets** | Auto-categorize issues, suggest responses, priority detection |
| **Expense Tracking** | Extract data from receipt photos, categorize expenses |

### Integration Columns

| Category | Columns |
|----------|---------|
| **AI Services** | Claude, OpenAI, Google Gemini, OpenRouter, Replicate |
| **Google** | Google Cloud, Cloud Vision, Google Maps |
| **Data** | Call API, JSON, CSV, XML |
| **Utilities** | Construct URL, Run Javascript Code |
| **Services** | Clearbit, Giphy, Gravatar, Hubspot, Pexels, Short.io, Yelp, ZenRows, OpenGraph.io, Radar |
| **App** | App special values, Browser, Device Info, Data Structures |

## Creating Columns via UI

### Adding a Column (Keyboard Shortcut - Recommended)

**Fastest method**: Use keyboard shortcut with browser automation

1. Navigate to the Data Editor (Data tab)
2. Click on the table where you want to add a column
3. Press **CMD+SHIFT+ENTER** (macOS) or **CTRL+SHIFT+ENTER** (Windows)
4. Type the column **Name**
5. **Select the Type** from dropdown (required - commits the name)
6. Configure type-specific options
7. Click **Save**

**Why this method:**
- Much faster than clicking through UI menus
- Works reliably with browser automation
- Direct keyboard access to column creation

### Adding a Column (Manual Method)

Alternative UI-based approach:

1. Go to Data Editor (Data tab)
2. Click any column header
3. Select "Add column right"
4. Configure:
   - **Name**: Column name
   - **Type**: Select from dropdown (searchable)
   - Type-specific options
5. Click Save

**Note**: Keyboard shortcut method is preferred for automation workflows.

### Column Type Picker
The Type dropdown is organized into categories:
- **Basic** - Storage types
- **Computed** - Calculation types
- **AI** - AI-powered processing
- **Integrations** - External services

Type in the search box to filter.

## Row ID Columns: The Primary Key System

**CRITICAL BEST PRACTICE**: Add a Row ID column to every table BEFORE creating relations.

### What Row ID Columns Are

Row ID columns are Glide's primary key system:
- **Unique identifier** for each row in a table
- **Automatically generated** when you add the column
- **Never changes** for a row (stable identifier)
- **Required for reliable relations** between tables

### How to Add Row ID Column

1. Navigate to the table in Data Editor
2. Add a new column (CMD+SHIFT+ENTER or click column header → "Add column right")
3. Name it "Row ID" (or similar - "ID", "Record ID")
4. Select column type: **Basic → Row ID**
5. Save
6. **The column will appear as the first column** in the table

### Why Add Row ID First

**Before creating relations:**
```
✅ RIGHT workflow:
1. Create tables
2. Add Row ID column to each table
3. Add foreign key columns (text columns to hold IDs)
4. Populate foreign key columns with Row ID values
5. Create relation columns

❌ WRONG workflow:
1. Create tables
2. Try to create relations immediately
3. Relations fail or use unreliable auto-generated $rowID
```

### Row ID vs $rowID

| Row ID Column | $rowID (auto-generated) |
|---------------|-------------------------|
| **Explicit** - you create it | **Implicit** - Glide adds it |
| **Visible** in Data Editor | **Hidden** (special column) |
| **Recommended** for relations | **Avoid** for relations |
| **Reliable** and stable | Can be less predictable |

**Best practice:** Always use explicit Row ID columns that you create, not Glide's auto-generated $rowID values.

### Example: Setting Up CRM Tables with Row IDs

**Step 1: Create tables and add Row ID to each**
```
Companies table:
  - Add column: "Row ID" (type: Row ID) ← Do this FIRST

Contacts table:
  - Add column: "Row ID" (type: Row ID) ← Do this FIRST

Deals table:
  - Add column: "Row ID" (type: Row ID) ← Do this FIRST
```

**Step 2: Add foreign key columns**
```
Contacts table:
  - Add column: "companyId" (type: Text) ← Will hold Row ID from Companies

Deals table:
  - Add column: "contactId" (type: Text) ← Will hold Row ID from Contac