3.6 Generating the Database Schema

The database schema defines the structure of your application's data storage. It creates the tables, columns, relationships, and constraints that will store and organize your application's information. In VibeMap, the database schema is automatically generated based on your features, user stories, and pages.

What Is a Database Schema?

A database schema includes:

• Tables - Data storage containers for different types of information

• Columns - Specific data fields within each table

• Data types - What kind of data each column stores (text, numbers, dates, etc.)

• Relationships - How tables connect to each other

• Constraints - Rules that ensure data integrity and consistency

• Indexes - Optimizations for faster data retrieval

Prerequisites

Before generating a database schema, you need:

• Project features - Schema is derived from your application's functionality

• User stories - Help determine what data needs to be stored

• Pages and components - Indicate what information users need to access

Note: The database schema is automatically generated when you create a project, but you can regenerate or modify it from the Database Schema section.

How to Generate the Database Schema

Automatic Generation (Recommended)

The database schema is created automatically during project creation. VibeMap analyzes your features, user stories, and pages to generate a comprehensive data structure.

Manual Generation

To regenerate the database schema or create additional schema elements:

1. Navigate to Database Schema: Click the "Database Schema" tab in your project sidebar

2. Click "Generate Database Schema": Creates a complete database structure

3. Wait for Processing: Takes 2-4 minutes for complex projects

4. Review Results: Schema appears across multiple tabs with interactive features

Generating Additional Schema Elements

If you need more tables, columns, or relationships:

1. Click "Generate Additional Schema Elements": Creates new elements without duplicates

2. AI analyzes existing schema: Avoids repetition and identifies gaps

3. Focuses on missing data needs: Covers areas you might have missed

4. Adds supporting tables: Creates tables for edge cases and admin functions

Database Schema Interface Overview

The Database Schema page is organized into multiple tabs, each offering different ways to view and manage your schema:

Available Tabs

• 📊 Diagram Tab - Interactive visual representation of your schema

• 📋 Tables Tab - Detailed table-by-table view with editing capabilities

• 🔗 Relationships Tab - Focused view of table relationships and constraints

• 📝 SQL Tab - Generated SQL statements for implementation

• ⚙️ Settings Tab - Schema configuration and export options

Detailed Tab Features

📊 Diagram Tab - Interactive Visual Schema

The Diagram tab provides a comprehensive visual representation of your database schema with powerful interactive features.

Top Toolbar Buttons

• 🔧 Fix All Foreign Keys - Automatically identifies and fixes missing or incorrect foreign key relationships

• ↔️ Horizontal Layout - Arranges tables in a horizontal flow for better wide-screen viewing

• ↕️ Vertical Layout - Arranges tables in a vertical flow for better tall-screen viewing

• 🔍 Fullscreen - Expands the diagram to full screen for detailed examination

• 🎯 Auto-Fit - Automatically adjusts zoom and position to show all tables optimally

• 💾 Export Diagram - Saves the current diagram view as an image file

Interactive Diagram Features

• Table relationships with connecting lines showing foreign key connections

• Primary keys (unique identifiers) highlighted for each table

• Foreign keys (relationships between tables) with directional arrows

• Column names and data types displayed within each table box

• Relationship types (one-to-one, one-to-many, many-to-many) indicated by line styles

• Drag and drop - Reposition tables by dragging them to better locations

• Zoom controls - Use mouse wheel or zoom buttons to adjust view size

• Pan functionality - Click and drag to move around large diagrams

Pin Line Editing

• Click on any relationship line to edit the connection between tables

• Modify relationship type (one-to-one, one-to-many, many-to-many)

• Add or remove foreign key constraints

• Edit column mappings between related tables

• Set cascade delete/update rules for referential integrity

📋 Tables Tab - Detailed Table Management

The Tables tab provides comprehensive table-by-table management capabilities.

Table List Features

• Expandable table cards showing column details

• Quick edit buttons for each table and column

• Table statistics (column count, relationship count, estimated size)

• Search and filter tables by name or type

Table Creation

• ➕ Create New Table button at the top of the tab

• Table wizard guides you through:

  • Table name and description

  • Primary key selection

  • Column definitions with data types

  • Index creation

  • Relationship establishment

Column Management

• ➕ Add Column button within each table

• Column editor includes:

  • Column name and data type selection

  • Constraint settings (NOT NULL, UNIQUE, DEFAULT values)

  • Foreign key relationship assignment

  • Index creation options

🔗 Relationships Tab - Relationship Management

The Relationships tab focuses specifically on table connections and constraints.

Relationship Overview

• Relationship list showing all table connections

• Relationship type indicators (1:1, 1:many, many:many)

• Constraint details for each relationship

• Cascade rules for delete and update operations

Relationship Editing

• Edit relationship by clicking on any relationship entry

• Modify constraint types and cascade rules

• Add new relationships between existing tables

• Remove relationships that are no longer needed

📝 SQL Tab - Generated SQL Statements

The SQL tab provides ready-to-use SQL code for implementing your schema.

Generated SQL Sections

• Table Creation Scripts - Complete CREATE TABLE statements

• Index Creation Scripts - Performance optimization queries

• Foreign Key Constraints - Relationship enforcement

• Data Seeding Scripts - Sample data for testing

• Migration Scripts - Version-controlled schema changes

SQL Features

• Copy to clipboard buttons for each SQL section

• Export all SQL as a single file

• Database-specific formatting (PostgreSQL, MySQL, SQLite)

• Syntax highlighting for better readability

• Validation checks for SQL correctness

⚙️ Settings Tab - Schema Configuration

The Settings tab provides configuration and export options for your schema.

Schema Configuration

• Database type selection (PostgreSQL, MySQL, SQLite, etc.)

• Naming convention preferences (camelCase, snake_case, PascalCase)

• Default data types for common column types

• Index creation preferences (automatic vs manual)

Export Options

• Export as JSON - Schema structure for programmatic use

• Export as XML - Schema structure for documentation tools

• Export as PDF - Printable schema documentation

• Export as Image - Visual diagram for presentations

Schema Organization

Tables are typically organized into categories:

• 👤 User Management - Users, roles, permissions, profiles

• 🔐 Authentication - Sessions, tokens, password resets

• 📊 Core Data - Main business entities and information

• 🔗 Relationships - Junction tables for many-to-many relationships

• 📝 Content - User-generated content, posts, comments

• ⚙️ System - Settings, configurations, logs, analytics

Example Schema Structure

Users Table
├── id (Primary Key)
├── email (Unique)
├── password_hash
├── first_name
├── last_name
├── created_at
└── updated_at

Projects Table
├── id (Primary Key)
├── user_id (Foreign Key → Users.id)
├── name
├── description
├── status
├── created_at
└── updated_at

Tasks Table
├── id (Primary Key)
├── project_id (Foreign Key → Projects.id)
├── user_id (Foreign Key → Users.id)
├── title
├── description
├── status
├── priority
├── due_date
├── created_at
└── updated_at

Managing Your Database Schema

Working with the Diagram Tab

The Diagram tab is your primary interface for visual schema management:

Viewing and Navigation

• Click on any table to see detailed column information in a popup

• Hover over relationship lines to see foreign key details

• Use mouse wheel to zoom in and out for better detail

• Click and drag to pan around large schemas

• Use toolbar buttons to switch between horizontal and vertical layouts

Interactive Editing

• Drag tables to reposition them for better organization

• Click on relationship lines to edit foreign key connections

• Double-click tables to open the table editor

• Right-click tables for context menu options (edit, delete, duplicate)

Diagram Toolbar Functions

• Fix All Foreign Keys - Automatically resolves relationship issues

• Layout Options - Switch between horizontal and vertical arrangements

• Fullscreen Mode - Expand diagram for detailed examination

• Export Diagram - Save current view as an image file

Working with the Tables Tab

The Tables tab provides detailed table management capabilities:

Table Management

• Expandable table cards show all columns and their properties

• Search and filter tables by name, type, or column count

• Table statistics display relationship counts and estimated sizes

• Quick actions for each table (edit, duplicate, delete)

Creating New Tables

1. Click "➕ Create New Table" button at the top

2. Table wizard opens with step-by-step guidance:

   • Enter table name and description

   • Select primary key column

   • Add columns with data types and constraints

   • Set up indexes and relationships

3. Preview and save to add to your schema

Column Management

1. Click "➕ Add Column" within any table

2. Column editor provides:

   • Column name and data type selection

   • Constraint settings (NOT NULL, UNIQUE, DEFAULT)

   • Foreign key relationship options

   • Index creation choices

3. Save changes to update the table structure

Working with the Relationships Tab

The Relationships tab focuses on table connections:

Relationship Overview

• List all relationships with type indicators (1:1, 1:many, many:many)

• Show constraint details including cascade rules

• Display relationship strength and cardinality information

Editing Relationships

1. Click on any relationship in the list

2. Relationship editor opens with options to:

   • Modify relationship type

   • Change cascade delete/update rules

   • Edit column mappings

   • Add or remove constraints

3. Save changes to update the relationship

Working with the SQL Tab

The SQL tab provides implementation-ready code:

Generated SQL Sections

• Table Creation Scripts - Ready-to-run CREATE TABLE statements

• Index Scripts - Performance optimization queries

• Foreign Key Constraints - Relationship enforcement code

• Data Seeding - Sample data for testing

• Migration Scripts - Version-controlled changes

SQL Management

• Copy individual sections to clipboard using copy buttons

• Export all SQL as a single file for implementation

• Choose database format (PostgreSQL, MySQL, SQLite)

• Validate SQL syntax before implementation

Working with the Settings Tab

The Settings tab provides configuration and export options:

Schema Configuration

• Database type selection - Choose your target database system

• Naming conventions - Set preferences for table and column naming

• Default data types - Configure common column type mappings

• Index preferences - Set automatic vs manual index creation

Export Options

• Export as JSON - Schema structure for programmatic use

• Export as XML - Schema structure for documentation tools

• Export as PDF - Printable schema documentation

• Export as Image - Visual diagram for presentations

Expected Outcomes

Typical Generation Results

• 8-15 core tables for most applications (varies by complexity)

• Clear relationships between related data entities

• Appropriate data types for different kinds of information

• Proper normalization following database best practices

Quality Indicators

Good database schemas will have:

• ✅ Logical table organization and naming

• ✅ Appropriate data types for each column

• ✅ Clear relationships between tables

• ✅ Proper primary and foreign key definitions

• ✅ Realistic and achievable data structure

Best Practices

Writing Effective Features for Schema Generation

To get better database schemas, ensure your features include:

• Data requirements - What information needs to be stored

• User workflows - How data flows through your application

• Business rules - Constraints and validation requirements

• Integration needs - External data sources or APIs

Example Good Feature for Schema Generation:

User Profile Management
Description: Users can create and manage detailed profiles including personal information, preferences, skills, work history, and social connections. Profiles are searchable, support file uploads for avatars and documents, and include privacy settings for different information types.

This generates:
- Users table with authentication fields
- User_profiles table with detailed information
- User_skills table for skill management
- User_documents table for file uploads
- User_privacy_settings table for privacy controls
- Proper relationships and constraints between tables

Troubleshooting

Common Issues

Too Few Tables Generated:

• Add more detail about data requirements in your features

• Include specific information types and data relationships

• Try the "Generate Additional Schema Elements" button

Tables Don't Match Your Data Needs:

• Edit generated tables to match your actual data requirements

• Add custom tables for unique data structures

• Modify relationships to match your business logic

Missing Relationships:

• Add foreign key relationships between related tables

• Consider many-to-many relationships that might be needed

• Review your features for implicit data connections

Data Types Seem Wrong:

• Edit column data types to match your specific needs

• Consider performance implications of data type choices

• Add appropriate constraints and validation rules

Next Steps

After generating the database schema:

1. Review table structure to ensure it matches your data needs

2. Validate relationships with your development team

3. Generate file structure - schema helps determine application architecture

4. Plan database implementation using the generated SQL

5. Consider data migration strategies for existing systems

The database schema provides the foundation for your application's data storage. A well-designed schema leads to better performance, easier maintenance, and more reliable data management.