Xano Documentation
  • 👋Welcome to Xano!
  • 🌟Frequently Asked Questions
  • 🔐Security & Compliance (Trust Center)
  • 🙏Feature Requests
  • 💔Known Issues
  • Before You Begin
    • Using These Docs
    • Where should I start?
    • Set Up a Free Xano Account
    • Key Concepts
    • The Development Life Cycle
    • Navigating Xano
    • Plans & Pricing
  • The Database
    • Designing your Database
    • Database Basics
      • Using the Xano Database
      • Field Types
      • Relationships
      • Database Views
      • Export and Sharing
      • Data Sources
    • Migrating your Data
      • Airtable to Xano
      • Supabase to Xano
      • CSV Import & Export
    • Database Performance and Maintenance
      • Storage
      • Indexing
      • Maintenance
      • Schema Versioning
  • 🛠️The Function Stack
    • Building with Visual Development
      • APIs
        • Swagger (OpenAPI Documentation)
      • Custom Functions
        • Async Functions
      • Background Tasks
      • Triggers
      • Middleware
      • Configuring Expressions
      • Working with Data
    • Functions
      • AI Tools
      • Database Requests
        • Query All Records
          • External Filtering Examples
        • Get Record
        • Add Record
        • Edit Record
        • Add or Edit Record
        • Patch Record
        • Delete Record
        • Bulk Operations
        • Database Transaction
        • External Database Query
        • Direct Database Query
        • Get Database Schema
      • Data Manipulation
        • Create Variable
        • Update Variable
        • Conditional
        • Switch
        • Loops
        • Math
        • Arrays
        • Objects
        • Text
      • Security
      • APIs & Lambdas
        • Realtime Functions
        • External API Request
        • Lambda Functions
      • Data Caching (Redis)
      • Custom Functions
      • Utility Functions
      • File Storage
      • Cloud Services
    • Filters
      • Manipulation
      • Math
      • Timestamp
      • Text
      • Array
      • Transform
      • Conversion
      • Comparison
      • Security
    • Data Types
      • Text
      • Expression
      • Array
      • Object
      • Integer
      • Decimal
      • Boolean
      • Timestamp
      • Null
    • Environment Variables
    • Additional Features
      • Response Caching
  • Testing and Debugging
    • Testing and Debugging Function Stacks
    • Unit Tests
    • Test Suites
  • CI/CD
  • File Storage
    • File Storage in Xano
    • Private File Storage
  • Realtime
    • Realtime in Xano
    • Channel Permissions
    • Realtime in Webflow
  • Maintenance, Monitoring, and Logging
    • Statement Explorer
    • Request History
    • Instance Dashboard
      • Memory Usage
  • Building Backend Features
    • User Authentication & User Data
      • Separating User Data
      • Restricting Access (RBAC)
      • OAuth (SSO)
    • Webhooks
    • Messaging
    • Emails
    • Custom Report Generation
    • Fuzzy Search
    • Chatbots
  • Xano Features
    • Snippets
    • Instance Settings
      • Release Track Preferences
      • Static IP (Outgoing)
      • Change Server Region
      • Direct Database Connector
      • Backup and Restore
      • Security Policy
    • Advanced Back-end Features
      • Xano Link
      • Developer API (Deprecated)
    • Metadata API
      • Master Metadata API
      • Tables and Schema
      • Content
      • Search
      • File
      • Request History
      • Workspace Import and Export
      • Token Scopes Reference
  • Xano AI
    • Building a Backend Using AI
    • Get Started Assistant
    • AI Database Assistant
    • AI Lambda Assistant
    • AI SQL Assistant
    • API Request Assistant
    • Template Engine
    • Streaming APIs
  • AI Tools
    • MCP Servers
      • Connecting Clients
      • MCP Functions
  • Xano Transform
    • Using Xano Transform
  • Xano Actions
    • What are Actions?
    • Browse Actions
  • Team Collaboration
    • Realtime Collaboration
    • Managing Team Members
    • Branching & Merging
    • Role-based Access Control (RBAC)
  • Agencies
    • Xano for Agencies
    • Agency Features
      • Agency Dashboard
      • Client Invite
      • Transfer Ownership
      • Agency Profile
      • Commission
      • Private Marketplace
  • Enterprise
    • Xano for Enterprise
    • Enterprise Features
      • Microservices
      • Tenant Center
      • Compliance Center
      • Security Policy
      • Instance Activity
      • Deployment
      • RBAC (Role-based Access Control)
      • Xano Link
  • Your Xano Account
    • Account Page
    • Billing
    • Referrals & Commissions
  • Troubleshooting & Support
    • Error Reference
    • Troubleshooting Performance
      • When a single workflow feels slow
      • When everything feels slow
      • RAM Usage
      • Function Stack Performance
    • Getting Help
      • Granting Access
      • Community Code of Conduct
      • Community Content Modification Policy
  • Special Pricing
    • Students & Education
    • Non-Profits
  • Security
    • Best Practices
Powered by GitBook
On this page
  • Table Relationships
  • Database Fields
  • Planning for the Future

Was this helpful?

  1. The Database

Designing your Database

Last updated 15 hours ago

Was this helpful?

Quick Summary

Good database design starts with organizing your information into logical groups, like putting customer details in one table and order history in another. These groups are then connected through common identifiers using a - like a customer ID that links a person to all their orders - which helps maintain accuracy and avoid duplicating information.

Think of designing a database like organizing your home. Before you buy storage containers or rearrange your furniture, you need a plan. The same goes for databases - careful planning prevents headaches later.

Hint

Use a tool like to help you when designing your database.

Start by listing everything you need to store. If you're building a bookstore database, you'll need to track books, authors, customers, and sales. Just like you wouldn't store your kitchen items in your bathroom, each type of information needs its own logical home in the database.

Let's draw these to look like individual items — these will represent the tables that we'll create.

Table Relationships

To illustrate multiple examples, we've added a Publishers table to our visualization.

Consider how these pieces connect. A book has one or more authors, and an author can write multiple books. A customer can buy many books, and a book can be bought by many customers. These relationships are crucial - they're like the hallways connecting rooms in your house, allowing you to move naturally between related information.

One-to-One

  • Each thing on one side matches exactly one thing on the other side. Like a person and their social security number: one person has one number, and each number belongs to one person

One-to-Many

  • One thing on one side can connect to multiple things on the other side, but those multiple things each only connect back to one thing. Like a mother and her children: one mother can have many children, but each child has only one mother

Many-to-Many

  • Things on both sides can connect to multiple things on the other side. Like students and classes: one student takes many classes, and each class has many students

For our book store example, let's visualize the relationships between our tables.

Database Fields

Think about the essential characteristics that describe each thing. Just like how a person's profile might include their name, birthday, and contact info, each table should contain the core pieces of information that defines that thing. When referring to all of the fields in a database table as a whole, we'll call this schema.

Let's use our bookstore example:

  • For Books: You'll want the ISBN (like a book's fingerprint), title, publication date, current price, and maybe format (hardcover/paperback). You don't need to store the author's name here - that's what the connection to the Authors table is for.

  • For Authors: You'll store their name, perhaps birth date, nationality, and a brief biography. You don't need to store a list of their books - the relationship between tables handles that.

  • For Customers: You'll want their name, contact information, shipping addresses, and maybe their preferences or a membership status. You don't need to store their purchase history here - that's tracked through the Sales table.

"Does this information describe the core thing I'm tracking, or is it really about something else?" If you find yourself wanting to store a list of things (like "all books by this author" or "all orders from this customer"), that's usually a sign you need a relationship between tables rather than storing that data directly.

Consider whether the information might change over time. For example, book prices change frequently, so you might want both a "currentPrice" in the Books table and a "salePrice" in the Sales Items table. This lets you track both what a book costs now and what customers actually paid for it in the past.

Planning for the Future

Think about what information you'll need to find quickly. Just as you might keep frequently used items in easily accessible drawers, consider what data you'll search for most often. This helps you decide how to organize and your information.

Think about how information might expand in the future. Initially, you might only need basic book formats (hardcover and paperback). But what happens when you want to add audiobooks? You'll need new fields like runtime, narrator, and audio format. Instead of hard-coding format types, you could create a separate formats table, and use a table reference in your books table that lets you add new types without changing your core structure.

Suppose you create a database table for storing book formats directly within the books table:

Books Table

BookID
Title
Author
Hardcover
Paperback
Audiobook

This design is inflexible because each time you introduce a new format, you must alter the table structure. Instead, use a separate formats table and establish a relationship with the books table.

Books Table

BookID
Title
Author

Formats Table

FormatID
FormatType

BookFormats Table

BookID
FormatID

This flexible table design allows you to add new formats easily without changing the core structure.

Finally, remember that simple is usually better. Like a well-organized home where everything has its place, a good database design should feel natural and intuitive. If you find yourself creating complicated structures to store simple information, step back and reconsider your approach.

Remember: A well-designed database makes everything else easier.

Watch out for duplicate information. If you're storing an author's contact details, store them once and reference them when needed, rather than copying them into every book record. This is like having one toolbox in your garage instead of keeping duplicate tools in every room. In Xano, this is accomplished with .

How many fields is 'too many'? This is not a black-and-white question to answer. Some tables can have a significant number of fields, but the dataset is small — this is usually okay. If you expect this table to grow in size over time, it's always better to split data types into separate tables — for example, if users have companies attached, you should probably store those companies in a separate table and use .

relationships
Excalidraw
table reference fields
Using Excalidraw to begin the database design process.