Schema Design Guide

Master the art of database schema design with Endora's visual builder.

Learn how to create efficient, scalable database schemas that power your APIs. This guide covers everything from basic table creation to complex relationship modeling.

What You'll Learn

• Visual schema design principles
• Table and column creation
• Relationship modeling
• Data validation and constraints
• Performance optimization
• Schema best practices

Schema Design Overview

Understanding Database Schemas

What is a Schema?

A database schema is the blueprint of your database. It defines:

• Tables: Containers for your data
• Columns: Individual data fields
• Relationships: How tables connect
• Constraints: Rules and validations

Why Schema Design Matters

Foundation of Your API

Your schema design directly impacts your API's performance, scalability, and usability.

Data Integrity

Proper schema design ensures data consistency and prevents errors.

Future-Proofing

Well-designed schemas are easier to extend and modify as your application grows.

Getting Started with Schema Design

Schema Planning

Before you start building, consider:

• What data do you need to store?
• How will the data be used?
• What relationships exist between data?
• What validations are required?

---

Data Types and Constraints

Supported Data Types

Type	Description	Example
text	Text data	"Hello World"
integer	Whole numbers	42
int	Whole numbers	3
bigint	Big numbers	364357893
boolean	True/false values	true
date	Date values	"2024-01-01"
timestamp	Date and time	"2025-10-30 18:00:00"
timestampz	Date, time and zone	"2025-10-30 18:00:00+07"
json	JSON objects	{"key": "value"}
uuid	Unique identifiers	"550e8400-e29b-41d4-a716-446655440000"

Common Constraints

• Primary Key: Unique identifier for each record
• Foreign Key: Reference to another table

---

Building Your Tables

Adding Tables

1. Click "New Schema" in Table Editor Tab: Create a new table
2. Name Your Table: Use descriptive, plural names (e.g., "users", "products")
3. Add Columns: Define the data structure
4. Add Relationship: Define relationship between table.

Table Design Best Practices

Use Plural Names

Table names should be plural (users, products, orders)

Include Timestamps

Always add created_at and updated_at columns

Primary Keys

Primary Keys will automatically added once the schema has created.

---

Reuse Schema

All your created schemas can be reused across multiple projects with just one click. There’s no need to redefine tables or re-enter fields manually once a schema is built, it becomes instantly available for use in any new project.

When you select a saved schema, all its fields, relationships, and configurations are automatically imported, allowing you to maintain consistency and accelerate your development process. This feature ensures that you can easily standardize your data models and scale your projects efficiently.

---

File Import

Creating a database schema is no longer a complex or time-consuming process. With Endora’s File Import feature, you can instantly generate your schema by simply dragging and dropping a CSV or JSON file. The system will automatically detect and create tables, fields, and data types based on your file’s structure giving you a ready-to-use schema in seconds.

Accept Files

JSON or CSV

---

Flexible Schema Editing

Once a schema is created, every field remains fully editable you can easily modify field names, data types, and configurations directly from the interface. This flexibility allows you to refine your schema as your data model evolves, without needing to recreate it from scratch.

Whether you’re adjusting field names for clarity or changing data types for better accuracy, Endora ensures that schema editing is fast, intuitive, and seamless.

---

Add Column

Your schema remains completely flexible even after creation. You can add new columns at any time to accommodate changing data requirements or evolving project needs. Each new column can be customized by name, type, and configuration ensuring your schema always stays up to date with your application’s growth.

Common Table Patterns

User Management

users
├── id (Primary Key)
├── email
├── name
├── password_hash
├── created_at
└── updated_at

E-commerce

products
├── id (Primary Key)
├── name
├── description
├── price
├── category_id (Foreign Key)
├── created_at
└── updated_at

Content Management

posts
├── id (Primary Key)
├── title
├── content
├── author_id (Foreign Key)
├── published_at
├── created_at
└── updated_at

Creating Relationships

Relationship Types

One-to-One

Each record in Table A relates to exactly one record in Table B.

users ←→ profiles

One-to-Many

One record in Table A can relate to many records in Table B.

users ←→ posts
categories ←→ products

Many-to-One

Many record in Table A can relate to one records in Table B.

posts ←→ users
products ←→ categories

Many-to-Many

Records in both tables can relate to multiple records in the other.

users ←→ roles (through user_roles)
posts ←→ tags (through post_tags)

Setting Up Relationships

1. Identify Connections: Determine how tables should relate
2. Add Foreign Keys: Create references between tables
3. Test Relationships: Verify connections work correctly

Relationship Best Practices

Use Descriptive Foreign Keys

Choose tables you want to add relationship then choose relationship type for tables. Once you created you'll get a foriegn column and cascade rule automatically.

Testing Your Schema

Schema Validation

Before generating your API, validate your schema:

1. Check Relationships: Ensure all foreign keys are valid
2. Verify Constraints: Confirm all validation rules are correct
3. Test Data Types: Ensure appropriate data types are used
4. Review Performance: Check for missing indexes

Common Schema Issues

Common Mistakes

• Missing Primary Keys: Every table needs a unique identifier
• Orphaned Records: Foreign keys without valid references
• Data Type Mismatches: Inconsistent data types across relationships
• Missing Indexes: Poor query performance

Next Steps

Ready to Generate Your API?

1. Review Your Schema: Double-check all relationships and constraints
2. Save Your Schema: Click "Save Schema" to store your design
3. Generate API: Move on to API Generation
4. Test Endpoints: Use the built-in testing interface

Learn More

• API Generation Guide
• API Reference
• Authentication Setup

---

Ready to turn your schema into an API? Generate your API