Data Design Basics
Master data design fundamentals to build well-structured Buzzy applications. Learn how to organize information using Subtables and Linked Table Fields with practical examples and prompts.
Why Data Design Matters
Your data structure is the foundation of your application. Get it wrong, and every feature you build will be harder than it needs to be.
Think of data design like organizing a filing cabinet:
Good filing system: Each folder is clearly labeled, related documents are grouped together, finding anything takes seconds
Bad filing system: Random papers everywhere, duplicates scattered about, searching takes hours and you're never sure you found everything
Good data design:
Makes features easy to add
Keeps your app fast
Prevents data inconsistencies
Simplifies maintenance
Scales naturally as your app grows
Bad data design:
Requires constant workarounds
Slows down as data grows
Creates bugs and edge cases
Makes changes expensive
Forces you to rebuild later (painful!)
Visual comparison:
Good Data Design:
Bad Data Design:
Pro Tip: Invest 30-60 minutes planning your data structure before writing your first Buzzy AI v3 prompt. This saves hours of restructuring later. Use paper, a whiteboard, or a tool like draw.io to sketch your entities and relationships.
The Basics: Entities and Attributes
Entities (Things You Store)
An entity is a type of thing your app manages. In Buzzy, these become Datatables.
Non-technical explanation: Think of entities as the main "topics" or "categories" your app deals with. If your app is about recipes, your entities might be Recipes, Ingredients, and Cooks. If it's about project management, entities are Projects, Tasks, and Team Members.
Examples by industry:
E-commerce: Products, Orders, Customers, Reviews, Categories
School: Students, Teachers, Classes, Assignments, Grades
Medical: Patients, Doctors, Appointments, Prescriptions, Medical Records
Project Management: Projects, Tasks, Team Members, Comments, Milestones
Real Estate: Properties, Agents, Clients, Showings, Offers
How to identify entities (ask yourself these questions):
What are the main "things" or "nouns" I'm managing?
Do I need to store multiple instances? (many products, many customers)
Does this thing have information I need to track?
Will users create, view, update, or delete these things?
Practice exercise - Food Delivery App:
App description: "Users order food from restaurants, drivers deliver"
Entities identified:
✓ Users (customers placing orders)
✓ Restaurants (establishments offering food)
✓ Menu Items (what restaurants offer)
✓ Orders (user's food orders)
✓ Drivers (people delivering food)
✓ Deliveries (tracking delivery status)
NOT entities:
✗ "Hungry" (this is a state, not a thing)
✗ "Fast" (this is a quality, not a thing)
✗ "Ordering" (this is an action, not a thing)Attributes (Properties of Entities)
Attributes are the specific pieces of information about each entity. In Buzzy, these become fields in your Datatables.
Non-technical explanation: If an entity is a topic (like "Products"), attributes are the specific facts you know about each product - its name, price, description, etc. Just like a person has attributes (name, age, height), database entities have attributes too.
Example - Product Entity (E-commerce):
Product Datatable fields:
- name (text, required) - e.g., "Wireless Bluetooth Headphones"
- description (long text) - e.g., "Premium sound quality with 40hr battery"
- price (number, required) - e.g., 79.99
- in_stock (boolean/yes-no) - e.g., true
- inventory_count (number) - e.g., 45
- category (text or linked field) - e.g., "Electronics"
- image_url (image upload) - uploaded product photo
- created_date (date, auto-generated) - e.g., 2025-01-15
- sku (text, unique) - e.g., "BT-HEAD-001"Choosing attributes (critical questions):
What information do I need to display to users?
What do users need to search or filter by?
What's required for business rules and calculations?
What do I need for reporting and analytics?
What validation or security rules apply?
Beginner's checklist for each entity:
Data Types Matter
Choose the right data type for each attribute - this affects how Buzzy handles validation, display, and calculations.
Buzzy field types explained for beginners:
Text (short)
Names, titles, short values
Product name, customer name, email
Long descriptions, multi-paragraph content
Text (long)
Descriptions, notes, content
Product description, blog post, instructions
Just a name or short value
Number
Values you calculate with
Price, quantity, age, rating
Phone numbers (use text), zip codes
Date/Time
Dates, timestamps
Created date, due date, appointment time
Age in years (calculate from birthdate)
Boolean (Yes/No)
True/false states
Is active? Is published? Is complete?
Status with 3+ options
Select/Dropdown
Fixed set of options
Status (draft/published), priority (low/medium/high)
Values that should be calculated
Image/File
Uploaded media
Product photo, profile picture, PDF document
URLs to external images
Subtable
One-to-many relationships
Order → Order Items, Project → Tasks
Many-to-many relationships
Linked Table Field
Many-to-many relationships
Products ↔ Categories, Students ↔ Classes
Simple one-to-many (use Subtable)
Real-world data type decisions:
Example: Building a Recipe App
Recipe Datatable:
✓ name: Text (short) - "Chocolate Chip Cookies"
✓ instructions: Text (long) - "1. Preheat oven to 375°F. 2. Mix butter..."
✓ prep_time: Number - 15 (minutes)
✓ servings: Number - 24
✓ is_vegetarian: Boolean - true
✓ difficulty: Select/Dropdown - "Easy", "Medium", "Hard"
✓ photo: Image - uploaded photo of finished dish
✓ created_date: Date - auto-generated timestamp
✓ ingredients: Subtable - links to Ingredients datatable
✓ categories: Linked Table Field - links to Categories (desserts, cookies, etc.)
Common mistakes to avoid:
✗ prep_time as Text - can't calculate total time
✗ servings as Text - can't do portion math
✗ difficulty as Text - can't filter/sort consistently
✗ phone_number as Number - loses leading zerosCommon Mistakes to Avoid:
Storing numbers as text: If you'll calculate with it, make it a number field. Price should be number (79.99), not text ("$79.99").
Phone numbers as numbers: Store as text to preserve formatting and leading zeros. "(555) 123-4567" won't work as a number.
Using text for status: Use Select/Dropdown for finite options like "pending/approved/rejected" so you can filter and validate consistently.
ZIP codes as numbers: Text field preserves leading zeros. "01234" becomes 1234 as a number.
Sample Buzzy AI v3 prompt including data types:
"Create a Products datatable with these fields:
- name (text, required)
- description (long text)
- price (number, required, minimum 0)
- in_stock (boolean, default true)
- category (dropdown with options: Electronics, Clothing, Home, Food)
- main_image (image upload)
- created_date (date, auto-generated)
- inventory_count (number, default 0)"Relationships: How Data Connects
Entities don't exist in isolation—they relate to each other. Understanding relationships is the most important part of data design. Get this wrong and your app will be frustrating to build and use.
Visual overview of relationship types:
One-to-Many (Most Common) - Buzzy Subtables
Definition: One instance of Entity A can relate to many instances of Entity B, but each B belongs to only one A.
Non-technical explanation: Think of it like a tree structure. A tree (parent) has many branches (children), but each branch belongs to only one tree. Or think of a book with chapters - one book has many chapters, but each chapter belongs to only one book.
Examples:
One customer can place many orders (but each order belongs to one customer)
One blog post can have many comments (but each comment belongs to one post)
One project can have many tasks (but each task belongs to one project)
One invoice can have many line items (but each line item belongs to one invoice)
How to implement in Buzzy using Subtables:
Create parent Datatable (e.g., Invoices)
Create child Datatable (e.g., Invoice Lines)
In the parent Datatable, add a Subtable field that points to the child Datatable
Buzzy automatically manages the
embeddingRowIDrelationship (foreign key)
Example - Invoice → Invoice Lines:
Invoices Datatable:
- _id (auto-generated)
- invoice_number
- customer_name
- invoice_lines (Subtable field → Invoice Lines datatable)
Invoice Lines Datatable:
- _id (auto-generated)
- embeddingRowID (auto-managed by Buzzy, links to parent Invoice)
- description
- amount
- quantityWhen viewing an Invoice: Buzzy automatically shows all related Invoice Lines through the Subtable field.
Visual representation:
Invoice #1001 Invoice #1002
├─ Line 1: Widget × 5 ├─ Line 1: Gadget × 2
├─ Line 2: Gadget × 2 └─ Line 2: Doohickey × 1
└─ Line 3: Gizmo × 1More real-world examples:
Blog: One post → many comments (each comment belongs to one post)
Customer: One customer → many orders (each order belongs to one customer)
Album: One music album → many songs (each song belongs to one album)
Course: One course → many lessons (each lesson belongs to one course)
Trip: One vacation trip → many expenses (each expense belongs to one trip)
Sample Buzzy AI v3 prompts for Subtables:
Example 1 - Simple Invoice:
"Create an Invoices datatable with these fields:
- invoice_number (text, unique, auto-generated)
- customer_name (text, required)
- invoice_date (date, default today)
- total_amount (number, calculated from line items)
- status (dropdown: Draft, Sent, Paid)
Add a subtable field called 'line_items' that links to an Invoice Lines datatable with:
- description (text, required)
- quantity (number, default 1)
- unit_price (number, required)
- line_total (formula: quantity * unit_price)
Users should be able to add, edit, and delete line items directly from the invoice screen."Example 2 - Project with Tasks:
"Create a Projects datatable with a tasks subtable:
Projects datatable:
- project_name (text, required)
- description (long text)
- start_date (date)
- status (dropdown: Planning, In Progress, Completed)
- tasks (subtable → Tasks datatable)
Tasks datatable (subtable of Projects):
- task_name (text, required)
- assigned_to (text)
- due_date (date)
- status (dropdown: To Do, In Progress, Done)
- priority (dropdown: Low, Medium, High)
Show tasks within each project detail screen."Example 3 - Blog with Comments:
"Build a blog app where:
Posts datatable:
- title (text, required)
- content (long text, required)
- author (text)
- published_date (date)
- comments (subtable → Comments datatable)
Comments datatable (subtable of Posts):
- commenter_name (text, required)
- comment_text (long text, required)
- comment_date (date, auto-generated)
The post detail screen should show all comments below the post content. Allow users to add new comments."When to use Subtables: This covers 80% of relationships in Buzzy apps. Use Subtables when:
Things naturally "belong to" parent records
You need to add/edit children within parent screen
Deleting parent should cascade to children
Each child has only one parent
Many-to-Many (More Complex) - Buzzy Linked Table Fields
Definition: Many instances of Entity A can relate to many instances of Entity B, and vice versa.
Examples:
Students and Classes (students take multiple classes, classes have multiple students)
Products and Categories (products can be in multiple categories, categories contain multiple products)
Tags and Articles (articles can have multiple tags, tags apply to multiple articles)
Projects and Team Members (projects have multiple members, members work on multiple projects)
How to implement in Buzzy using Linked Table Fields:
Create both Datatables (e.g., Students and Classes)
Add a Linked Table Field in one or both tables
Buzzy uses
crossAppRowIDto manage the relationshipThe field stores both a reference ID and display value
Example - Students ↔ Classes:
Students Datatable:
- _id (auto-generated)
- name
- email
- classes (Linked Table Field → Classes datatable)
Classes Datatable:
- _id (auto-generated)
- class_name
- instructor
- students (Linked Table Field → Students datatable, optional)
When you add a student to a class:
{
value: "class_123", // crossAppRowID
label: "Introduction to Physics"
}Alternative pattern using junction table:
Enrollments Datatable (Subtable of Students):
- _id (auto-generated)
- embeddingRowID (links to Student)
- class (Linked Table Field → Classes)
- enrollment_date
- gradeVisual representation:
Student: Alice Classes:
├─ Math 101 ←──────────┬─ Math 101 (has Alice, Bob, Carol)
├─ History 201 ←────────│─ History 201 (has Alice, David)
└─ Art 301 ←────────────│─ Physics 101 (has Bob, Carol)
└─ Art 301 (has Alice)
Student: Bob
├─ Math 101
└─ Physics 101More real-world examples:
Products ↔ Categories: Electronics can be in "Tech" AND "Gifts"; Clothing in "Fashion" AND "Sale Items"
Movies ↔ Actors: One movie has many actors, one actor appears in many movies
Articles ↔ Tags: One article can have tags "Technology", "AI", "Tutorial"; one tag applies to many articles
Recipes ↔ Dietary Restrictions: One recipe can be "Vegetarian" AND "Gluten-Free"; one restriction applies to many recipes
Projects ↔ Skills Required: One project needs "JavaScript", "Design", "Marketing"; one skill used in many projects
Sample Buzzy AI v3 prompts for Linked Table Fields:
Example 1 - Students and Classes (Simple N:M):
"Build a class enrollment system:
Students datatable:
- student_name (text, required)
- email (text, required)
- enrolled_classes (linked table field → Classes, multi-select)
Classes datatable:
- class_name (text, required)
- instructor (text)
- schedule (text)
- students_enrolled (linked table field → Students, multi-select)
Students can enroll in multiple classes.
Classes can have multiple students.
Show enrolled classes on student detail screen.
Show enrolled students on class detail screen."Example 2 - Products and Categories (N:M with Display):
"Create a product catalog:
Products datatable:
- product_name (text, required)
- price (number, required)
- categories (linked table field → Categories, multi-select)
Categories datatable:
- category_name (text, required, unique)
- description (text)
Products can be in multiple categories (a hoodie could be in 'Clothing', 'Sale', and 'New Arrivals').
Product list screen should show category tags.
Category screen should show all products in that category."Example 3 - Junction Table Pattern (N:M with Extra Info):
"Build a project management system where team members have roles:
Projects datatable:
- project_name (text, required)
- description (long text)
- team_assignments (subtable → Team Assignments datatable)
Team Assignments datatable (subtable of Projects):
- team_member (linked table field → Users)
- role (dropdown: Lead, Developer, Designer, QA)
- hours_allocated (number)
- start_date (date)
Team Members (Users) datatable:
- name (text, required)
- email (text, required)
- skills (text)
This pattern lets you track WHO is on each project AND their ROLE and HOURS."When to choose which pattern:
Simple N:M, no extra info
✅ Students ↔ Classes
Need to store info about relationship
✅ Projects ↔ Team Members (with role, hours)
Just need to link records
✅ Products ↔ Categories
Need relationship history
✅ Users ↔ Skills (with proficiency level, years)
Learn more about Linked Table Fields
One-to-One (Rare)
Definition: One instance of Entity A relates to exactly one instance of Entity B.
Examples:
User and UserProfile (each user has one profile, each profile belongs to one user)
Employee and EmployeeBadge (each employee has one badge, each badge belongs to one employee)
When to use:
Splitting large entities for organization
Separating sensitive data
Optional extended information
In most cases: Just combine into one entity unless there's a strong reason to separate.
Normalization: Avoiding Duplication
Normalization means organizing data to reduce duplication and maintain consistency.
The Problem: Denormalized Data
Bad Example - Order table:
order_id | customer_name | customer_email | customer_address | product_name | product_price
1 | John Doe | [email protected] | 123 Main St | Widget | 19.99
2 | John Doe | [email protected] | 123 Main St | Gadget | 29.99Problems:
Customer info duplicated (what if address changes?)
Product info duplicated (what if price changes?)
Typos create "different" customers
Hard to update consistently
The Solution: Normalized Data
Good Example:
Customers table:
customer_id | name | email | address
1 | John Doe | [email protected] | 123 Main StProducts table:
product_id | name | price
1 | Widget | 19.99
2 | Gadget | 29.99Orders table:
order_id | customer_id | product_id | order_date
1 | 1 | 1 | 2025-01-15
2 | 1 | 2 | 2025-01-16Benefits:
Update customer address once, affects all orders
Update product price once, historical orders unaffected
No duplication, no inconsistency
When to Denormalize
Sometimes duplication is okay:
Acceptable reasons:
Performance (calculated fields to avoid complex queries)
Historical snapshots (order should remember price at time of purchase)
Reporting (denormalized reporting tables separate from operational data)
Rule of thumb: Normalize by default. Denormalize only when you have a specific performance or business reason.
Common Patterns
1. User-Generated Content
Pattern: Users create content items (posts, listings, reviews, etc.)
Entities:
Users
Content Items (each has author_id linking to Users)
Example - Blog:
Users (id, name, email)
Posts (id, title, content, author_id, published_date)
2. Hierarchical Data
Pattern: Items can contain sub-items (categories, comments, tasks)
Entities:
Parent items
Child items (with parent_id)
Example - Comments:
Posts (id, title, content)
Comments (id, content, post_id, parent_comment_id)
parent_comment_id allows nested replies
3. Status Workflows
Pattern: Items move through defined states
Entities:
Main entity with status field
Optionally: StatusHistory table tracking changes
Example - Order Processing:
Orders (id, customer_id, status: pending/processing/shipped/delivered)
OrderHistory (id, order_id, old_status, new_status, changed_at)
4. Tagging and Categorization
Pattern: Items can be grouped or tagged flexibly
Entities:
Items
Tags
ItemTags (junction table)
Example - Recipe App:
Recipes (id, name, instructions)
Tags (id, name: "vegetarian", "quick", "dessert")
RecipeTags (recipe_id, tag_id)
Working with Buzzy's Data Model
Buzzy provides powerful features specifically designed for no-code data modeling:
Datatables - Your Core Data Structure
Your entities become Datatables in Buzzy. Each Datatable automatically includes:
_id: Unique identifier (auto-generated)embeddingRowID: Foreign key for Subtable relationships (auto-managed)authoranduserID: Track who created each recordviewersandteamViewers: Server-level access control
See complete Buzzy Datamodel Overview
Subtables - The Foundation of 1:M Relationships
Subtables are Buzzy's implementation of one-to-many relationships:
Parent record contains a Subtable field
Child records automatically linked via
embeddingRowIDChanges to child records don't affect parent
Can have multi-level nesting (grandparent → parent → child)
Common uses:
Invoices → Invoice Lines
Blog Posts → Comments
Projects → Tasks
Customers → Orders → Order Items
Linked Table Fields - The Foundation of N:M Relationships
Linked Table Fields connect many-to-many relationships:
Reference records in another Datatable
Store
crossAppRowIDand display labelCan be multi-select or single-select
Enables complex relationship graphs
Common uses:
Products ↔ Categories
Students ↔ Classes
Articles ↔ Tags
Projects ↔ Team Members
Learn more about Linked Table Fields
Formula Fields - Calculations and Validation
Use JSONATA formulas for:
Calculated fields:
quantity * priceConditional logic:
user.highestRole = "admin"Data validation:
dueDate > $now()Complex transformations
Learn more about Formula Fields
Server-Level Security
Design your data with security in mind from the start:
Viewers field: List specific users who can access a record
Team Viewers field: List teams who can access a record
Organizations: Multi-tenant architecture
Teams: Role-based access control
Security is enforced at the server/database level, not client-side.
Learn more about Security and Access Control
Data Design Checklist
Before building, verify your design:
Prompting Buzzy AI v3 with Your Data Design
Once you've designed your data, include clear relationship descriptions in your prompts:
Bad Prompt: "Build a recipe app"
Good Prompt for Buzzy: "Build a recipe app with the following datatables:
Recipes datatable:
name (text, required)
instructions (long text)
prep_time (number, minutes)
author (linked to Users)
ingredients (subtable → Ingredients datatable)
Ingredients datatable (subtable of Recipes):
ingredient_name (text, required)
quantity (text, e.g., '2 cups')
notes (text, optional)
Users datatable:
name (text, required)
email (text, required)
Relationships:
Each recipe belongs to one user (author)
Each recipe has multiple ingredients (one-to-many using subtable)
Users can create many recipes
Screens needed:
Recipe list showing all recipes with author names
Recipe detail showing ingredients in a subtable
Form to add new recipe with ability to add multiple ingredients"
Result: Buzzy generates an App Definition with proper Subtable relationships, requiring minimal adjustments.
Key tips for Buzzy AI prompts:
Explicitly state "subtable" for 1:M relationships
Explicitly state "linked field" for N:M relationships
Describe the relationships in plain language
Mention what should be shown on each screen
Specify any access control (viewers, team viewers)
Common Data Design Mistakes
1. Everything in One Table
Mistake: Cramming all data into one giant table
Fix: Separate into logical entities with relationships
2. Over-Normalization
Mistake: Creating too many tiny tables, making queries complex
Fix: Balance normalization with practicality
3. Wrong Data Types
Mistake: Storing numbers as text, dates as strings
Fix: Choose appropriate data types from the start
4. Missing Required Fields
Mistake: Making everything optional, then handling blank data everywhere
Fix: Mark essential fields as required
5. No Audit Trail
Mistake: Not tracking who created/modified data or when
Fix: Include created_by, created_at, modified_by, modified_at fields
Next Steps
Apply to user flows: Functional Design
See it in practice: Building Examples
Start building: Project Workflow
Remember: Good data design is iterative. Start with your best understanding, build, learn, and refine. Don't aim for perfection—aim for solid foundations that can evolve.
Last updated