Data Model

Database schema and data structure for Simple Manager Mobile

Overview

Simple Manager Mobile uses SQLite for local data storage. The data model is designed to be simple yet flexible, supporting various types of records with optional metadata.

Database Schema

Records Table

The records table is the primary storage for all record data:

CREATE TABLE records (
    id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    subtitle TEXT,
    metadata TEXT,
    type TEXT NOT NULL,
    userId TEXT,
    createdAt TEXT,
    updatedAt TEXT,
    isDeleted INTEGER
);

Field Specifications

id · TEXT · required

Primary key for the record. Uses TEXT type to support UUID or other string-based identifiers.

title · TEXT · required

Record title with a maximum length of 50 characters. Cannot be empty or duplicated.

subtitle · TEXT

Optional subtitle field for additional context.

metadata · TEXT

Optional field for storing JSON or other structured data as text.

type · TEXT · required

Required field indicating the record type or category.

userId · TEXT

Optional user identifier for multi-user scenarios.

createdAt · TEXT

ISO 8601 timestamp stored as text.

updatedAt · TEXT

ISO 8601 timestamp stored as text.

isDeleted · INTEGER

Boolean flag stored as INTEGER (0 or 1) for soft delete functionality.

Data Constraints

The data model enforces several important constraints:

• Title uniqueness: Record titles cannot be duplicated
• Title length: Maximum 50 characters
• Required fields: title and type must always have values
• Title minimum length: At least 3 characters
• Type minimum length: At least 3 characters

Database Connection

The application uses Expo SQLite for database management:

import * as SQLite from "expo-sqlite";

export const db = SQLite.openDatabaseSync("simple_manager.db");

Design Decisions

Why SQLite?

SQLite provides:

• Local-first data storage
• No network dependency
• Fast queries
• Built-in support through Expo

Soft Deletes

The isDeleted flag enables soft deletion, allowing records to be recovered and maintaining data integrity for audit purposes.

Text-based Timestamps

Timestamps are stored as TEXT in ISO 8601 format, making them human-readable and easily parseable across different platforms.

Related Topics

• Records
• Validation
• Database