Database Schema Documentation

Overview

The database uses IndexedDB with a three-store structure to manage tables, columns, and data records. This design provides flexibility while maintaining data integrity through relationships.

Key Features:

Automatic ID generation for all records
Foreign key relationships for data integrity
JSON storage for flexible data structures
Built-in timestamps for auditing

Database Stores

Tables Store

Field	Type	Description
id	auto	Unique identifier
name	string	Table name
created	date	Creation timestamp
modified	date	Last modified timestamp

Columns Store

Field	Type	Description
id	auto	Unique identifier
tableId	foreign key	References Tables.id
name	string	Column name
type	string	Data type (text, number, etc.)
order	number	Display order
options	json	Column-specific settings

Records Store

Field	Type	Description
id	auto	Unique identifier
tableId	foreign key	References Tables.id
data	json	Record data object
created	date	Creation timestamp
modified	date	Last modified timestamp

Data Relationships

The database uses a one-to-many relationship structure:

One table can have many columns (Tables → Columns)
One table can have many records (Tables → Records)

Example Data Structure:

{
    "Tables": {
        "id": 1,
        "name": "Employees",
        "created": "2024-01-01T12:00:00Z"
    },
    "Columns": [
        {
            "id": 1,
            "tableId": 1,
            "name": "Name",
            "type": "text"
        },
        {
            "id": 2,
            "tableId": 1,
            "name": "Age",
            "type": "number"
        }
    ],
    "Records": [
        {
            "id": 1,
            "tableId": 1,
            "data": {
                "1": "John Doe",
                "2": 30
            }
        }
    ]
}

Implementation Details

The schema is implemented using IndexedDB object stores:

// Database initialization
const request = indexedDB.open("DatabaseName", 1);

request.onupgradeneeded = (event) => {
    const db = event.target.result;

    // Tables Store
    const tablesStore = db.createObjectStore("tables", { 
        keyPath: "id", 
        autoIncrement: true 
    });
    tablesStore.createIndex("name", "name", { unique: true });

    // Columns Store
    const columnsStore = db.createObjectStore("columns", {
        keyPath: "id",
        autoIncrement: true
    });
    columnsStore.createIndex("tableId", "tableId");

    // Records Store
    const recordsStore = db.createObjectStore("records", {
        keyPath: "id",
        autoIncrement: true
    });
    recordsStore.createIndex("tableId", "tableId");
};

Important Notes:

All IDs are auto-incrementing integers
Indexes are created for frequent query patterns
Foreign keys are maintained at the application level
JSON data allows for flexible record structures

Usage Examples

Creating a New Table:

// Create table
const table = {
    name: "Employees",
    created: new Date(),
    modified: new Date()
};

// Add columns
const columns = [
    { name: "Name", type: "text", order: 0 },
    { name: "Age", type: "number", order: 1 },
    { name: "Photo", type: "url", order: 2 }
];

// Add record
const record = {
    data: {
        "name": "John Doe",
        "age": 30,
        "photo": "https://example.com/photo.jpg"
    }
};