Editing Schema

Learn how to create and modify tables, fields, and schema elements in ChartDB

Table Management

ChartDB provides intuitive tools for creating and managing database tables directly on the canvas.

Creating Tables

Using the Context Menu

The quickest way to create a table:

1. Right-click on Canvas

   Right-click on any empty space on the canvas to open the context menu.

2. Select 'New Table'

   Click New Table from the menu. A new table will be created at the clicked position.

3. Enter Edit Mode

   The table automatically enters edit mode, allowing you to configure it immediately.

ChartDB automatically assigns tables to the appropriate schema based on your database type. For PostgreSQL, new tables use the public schema by default. For MySQL, the default database schema is used.

Table Position

Tables are positioned at the exact point where you right-clicked. You can:

• Drag tables to reposition them
• Use the auto-layout feature to organize tables automatically
• Snap tables to grid (if enabled)

Table Edit Mode

Double-click on any table or click the Edit option from the table context menu to enter edit mode.

Editing Table Properties

Table Name

• Click on the table name at the top
• Type the new name
• Press Enter to save

Naming Conventions:

• Use lowercase with underscores (snake_case): user_profiles
• Use plural nouns: orders, customers
• Avoid special characters and spaces

Schema Assignment

For databases that support schemas (PostgreSQL, SQL Server):

• Select the schema from the dropdown
• Create new schemas if needed
• Leave empty for default schema

Table Color

Customize table appearance:

• Click the color picker
• Choose from preset colors
• Colors help visually group related tables

Available colors:

• #b067e9 (Purple)
• #ff6b8a (Pink)
• #4dee8a (Green)
• #ffe374 (Yellow)
• #7175fa (Blue)
• #42e0c0 (Teal)
• #ff6363 (Red)
• #8a61f5 (Violet)
• #b0b0b0 (Gray - for views)

Table Type

Toggle between regular table and view:

• Regular tables: Standard database tables
• Views: Database views (rendered in gray)
• Materialized Views: For supported databases

Comments

Add documentation to your tables:

• Click the comments field
• Enter descriptive text about the table’s purpose
• Comments are included in SQL exports

Managing Fields

Adding Fields

While in edit mode:

1. Click 'Add Field'

   Click the + Add Field button at the bottom of the field list.

2. Enter Field Name

   Type the field name in the input that appears.

3. Configure Field

   Set the field properties (see below).

Field Properties

name · string · required

The field name. Use snake_case naming convention:

• user_id
• created_at
• email_address

type · DataType · required

The data type. Available types depend on your database:

Common Types:

• INT, BIGINT, SMALLINT (integers)
• VARCHAR(n), TEXT (strings)
• DECIMAL(p,s), NUMERIC (decimals)
• DATE, TIMESTAMP, DATETIME (temporal)
• BOOLEAN (true/false)
• JSON, JSONB (PostgreSQL)
• UUID (PostgreSQL)
• ENUM (MySQL, PostgreSQL)

primaryKey · boolean · default: "false"

Mark this field as part of the primary key. Multiple fields can be selected for composite primary keys.

unique · boolean · default: "false"

Enforce unique values for this field across all rows.

nullable · boolean · default: "true"

Allow NULL values. Uncheck to make the field NOT NULL.

increment · boolean · default: "false"

Enable auto-increment for integer fields (AUTO_INCREMENT in MySQL, SERIAL in PostgreSQL).

isArray · boolean · default: "false"

For PostgreSQL: mark field as an array type (e.g., INTEGER[]).

characterMaximumLength · string

For VARCHAR and CHAR types, specify the maximum length:

• VARCHAR(255)
• CHAR(4)

precision · number

For DECIMAL/NUMERIC types, the total number of digits:

• DECIMAL(10, 2) - precision is 10

scale · number

For DECIMAL/NUMERIC types, the number of decimal places:

• DECIMAL(10, 2) - scale is 2

default · string

Default value for the field:

• 'active' (string)
• 0 (number)
• CURRENT_TIMESTAMP (function)
• NULL

collation · string

Character collation for string fields:

• utf8mb4_general_ci (MySQL)
• en_US.UTF-8 (PostgreSQL)

comments · string

Field-level documentation and comments.

check · string

Check constraint expression:

• age >= 0
• status IN ('active', 'inactive')

Field Toggles

Quick toggles for common field attributes:

PK

Primary Key

Toggle primary key status

U

Unique

Toggle unique constraint

NN

Not Null

Toggle nullable status

Reordering Fields

Fields can be reordered by:

1. Clicking and dragging the field handle (⋮)
2. Moving fields up or down in the list
3. The order affects both display and SQL export

Deleting Fields

Deleting a field that’s used in relationships will also remove those relationships. This action cannot be undone.

To delete a field:

1. Hover over the field in edit mode
2. Click the delete (trash) icon
3. Confirm the deletion

Table Context Menu

Right-click on a table (outside edit mode) to access:

1. Edit Table

   Opens the table in edit mode for modifying fields and properties.

2. Duplicate Table

   Creates a copy of the table:

   • Appends _copy to the table name
   • Copies all fields and their properties
   • Positions the copy slightly offset from the original
   • Does NOT copy relationships
3. Add Relationship

   Starts relationship creation mode with this table as the source. Click on another table to complete the relationship.

4. Delete Table

   Removes the table and all its relationships.

Indexes

Manage table indexes for performance optimization:

Creating Indexes

1. Open Table in Edit Mode

   Double-click the table or select Edit from the context menu.

2. Navigate to Indexes Tab

   Click on the Indexes tab (if available in your database type).

3. Add Index

   Click + Add Index and configure:

   • Index name
   • Fields to index (can select multiple)
   • Unique constraint
   • Index type (B-tree, Hash, etc.)

Primary Key Indexes

Primary key fields automatically create a PRIMARY index:

CREATE TABLE employees (
    emp_no INT PRIMARY KEY,  -- Automatically creates PRIMARY index
    name VARCHAR(100)
);

Check Constraints

Add validation rules to your tables:

Creating Check Constraints

CREATE TABLE salaries (
    emp_no INT,
    salary INT,
    from_date DATE,
    to_date DATE,
    CONSTRAINT salary_check CHECK (salary >= 0),
    CONSTRAINT date_check CHECK (to_date > from_date)
);

1. Open Constraints Tab

   In table edit mode, navigate to the Constraints tab.

2. Add Constraint

   Click + Add Check Constraint.

3. Define Constraint

   Enter:

   • Constraint name
   • Check expression (e.g., salary >= 0)

Table Width

Tables can be resized to accommodate long field names:

• Minimum width: 224px
• Medium width: 337px
• Maximum width: 450px

Drag the table edge to resize, or ChartDB will auto-adjust based on content.

Expanding/Collapsing Tables

Tables with many fields can be collapsed to save canvas space:

• Tables show up to 10 fields when collapsed
• Click Show more to expand and see all fields
• Click Show less to collapse
• The expanded property is saved with your diagram

Working with Views

Creating Database Views

Views are virtual tables based on queries:

1. Create View

   Right-click canvas → New View

2. Define Fields

   Add fields that will appear in the view result set

3. Visual Distinction

   Views are automatically colored gray (#b0b0b0) to distinguish them from tables

Views in ChartDB represent the structure of the view’s output, not the underlying query. For SQL export, views are exported as CREATE VIEW statements.

Best Practices

Naming Conventions

Tables:

• Use plural nouns: users, orders, products
• Snake_case: user_profiles, order_items

Fields:

• Snake_case: first_name, created_at
• Use _id suffix for foreign keys: user_id, product_id
• Use _at suffix for timestamps: created_at, updated_at

Primary Keys

• Always define a primary key
• Use auto-incrementing integers for most tables
• Use UUIDs for distributed systems
• Name PK fields consistently: id or {table}_id

Data Types

• Use appropriate sizes: SMALLINT for small numbers, BIGINT for large
• Use VARCHAR(n) with realistic limits, not VARCHAR(255) everywhere
• Use TEXT for unlimited length strings
• Use DECIMAL for currency, not FLOAT

Constraints

• Mark fields as NOT NULL when appropriate
• Use UNIQUE constraints for email, username, etc.
• Add check constraints for business rules
• Document constraints with comments

Comments

Add comments to:

• Tables explaining their purpose
• Complex fields requiring clarification
• Business rules and constraints
• Data retention policies

Common Patterns

Timestamps Pattern

CREATE TABLE users (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Soft Delete Pattern

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMP NULL,
    INDEX idx_deleted_at (deleted_at)
);

UUID Primary Key Pattern

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL,
    expires_at TIMESTAMP NOT NULL
);

Next Steps

Managing Relationships

Connect tables with foreign key relationships

Keyboard Shortcuts

Speed up your workflow with shortcuts

Using Areas

Organize tables into logical groups

Adding Notes

Document your schema with notes