Data Modeling Guide

Learn how to design robust, scalable data models that form the foundation of your Appivo applications.

Understanding Models

A Model represents a type of business object in your application. When you create a model, Appivo automatically:

• Creates the database table
• Generates API endpoints
• Handles data persistence
• Provides query capabilities

Model Structure

Every model consists of:

• Attributes - The fields that store data
• Relationships - Connections to other models
• Validations - Rules that ensure data quality
• System Fields - Automatically managed fields

Attribute Types

Choosing the Right Type

Use this decision guide:

1. Will users enter this data? Consider input widgets
2. Is it a finite set of values? Use State type
3. Could values be very long? Use Text instead of String
4. Does it represent a location? Use Coordinate
5. Does it require decimals? Use Float instead of Integer

Defining Relationships

Relationships connect models together, enabling you to build complex data structures.

One-to-Many

The most common relationship. One parent record relates to many child records.

Example: Customer Orders

Customer (1) ───────> (*) Order
One customer has many orders
Each order belongs to one customer

Implementation:

1. Create both models (Customer, Order)
2. Add a reference attribute to Order pointing to Customer
3. Appivo automatically creates the foreign key

Many-to-Many

Both sides can have multiple related records.

Example: Products and Categories

Product (*) <──────> (*) Category
Products can belong to multiple categories
Categories can contain multiple products

Implementation:

1. Create both models
2. Add a many-to-many relationship
3. Appivo creates a junction table automatically

One-to-One

Each record relates to exactly one record in another model.

Example: User Profile

User (1) ─────────── (1) Profile
Each user has exactly one profile
Each profile belongs to one user

Relationship Best Practices

1. Name relationships clearly - Use descriptive names that indicate the relationship
2. Consider cascade behavior - What happens when parent records are deleted?
3. Add appropriate indexes - Foreign keys are indexed automatically
4. Plan navigation - Consider how users will traverse relationships

Validation Rules

Protect data integrity with validation rules on attributes.

Available Validations

Validation Examples

Email Attribute

Attribute: Email
Type: String
Validations:
  - Required: true
  - Unique: true
  - Pattern: ^[^@\s]+@[^@\s]+\.[^@\s]+$

Quantity Attribute

Attribute: Quantity
Type: Integer
Validations:
  - Required: true
  - Min Value: 0
  - Max Value: 10000

Product Code

Attribute: ProductCode
Type: String
Validations:
  - Required: true
  - Unique: true
  - Pattern: ^[A-Z]{3}-[0-9]{4}$
  - Max Length: 8

Indexes

Indexes improve query performance for frequently searched fields.

Automatic Indexes

Appivo automatically creates indexes for:

• Primary keys (id field)
• Foreign keys (relationship fields)
• Unique constraints

Custom Indexes

Add custom indexes for:

• Fields used in filters
• Fields used in sorting
• Fields used in search

Index Best Practices

1. Index search fields - Any field users search by frequently
2. Don't over-index - Each index adds write overhead
3. Consider composite indexes - For queries filtering multiple fields
4. Monitor query performance - Add indexes where queries are slow

System Fields

Every model automatically includes these system-managed fields:

Optimistic Locking

The ver field prevents conflicts when multiple users edit the same record:

1. User A loads record (ver: 1)
2. User B loads record (ver: 1)
3. User A saves changes (ver becomes 2)
4. User B tries to save - conflict detected (expected ver: 1, found ver: 2)
5. User B must reload and retry

Common Patterns

Reference Data

Create models for lookup values:

Model: Status
Attributes:
  - Name (String, required)
  - Code (String, required, unique)
  - DisplayOrder (Integer)
  - Active (Boolean, default: true)

Audit Trail

Track all changes to important data:

Model: AuditLog
Attributes:
  - Action (State: CREATE, UPDATE, DELETE)
  - ModelName (String)
  - RecordId (String)
  - Changes (Text) - JSON of old/new values
  - User (Reference to User)
  - Timestamp (Date & Time)

Soft Delete

Mark records as deleted without removing them:

Model: Customer
Attributes:
  - ... (other fields)
  - Deleted (Boolean, default: false)
  - DeletedAt (Date & Time, optional)
  - DeletedBy (Reference to User, optional)

Hierarchical Data

Model tree structures:

Model: Category
Attributes:
  - Name (String, required)
  - Parent (Reference to Category, optional)
  - Path (String) - for quick ancestry lookup
  - Level (Integer) - depth in tree

Best Practices

Naming Conventions

• Use singular nouns for model names (Customer, not Customers)
• Use PascalCase for attribute names (FirstName, not first_name)
• Be descriptive but concise
• Avoid abbreviations unless universally understood

Data Normalization

• Store each piece of information in one place only
• Use references instead of duplicating data
• Create separate models for repeating groups

Planning Your Schema

1. List your business objects - What entities does your app manage?
2. Identify attributes - What information do you store for each?
3. Define relationships - How do entities connect?
4. Add validations - What rules ensure data quality?
5. Plan for growth - Consider future requirements

Performance Considerations

• Keep models focused - don't create mega-models with dozens of fields
• Index fields used in filters and searches
• Use pagination for large data sets
• Consider denormalization for read-heavy operations

Next Steps

• Rules and Actions - Automate based on data changes
• User Interfaces - Display and edit data
• Validation Guide - Advanced validation patterns