Collections (Tables)

Naming:

• •Table name = Collection name
• •Use PascalCase (e.g., ThermalPlant, GaugingStation)
• •Always singular form (not plural)

Required structure:

• •Must have id INTEGER PRIMARY KEY AUTOINCREMENT
• •Must end with STRICT
• •Every database must have a Configuration table

CREATE TABLE CollectionName (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- columns here
) STRICT;

Scalar Attributes

Naming:

• •Use snake_case (e.g., minimum_generation, some_example_value)
• •Singular form

Special columns:

• •label: Must be TEXT UNIQUE NOT NULL
• •date_* columns: Must be TEXT (mapped to DateTime)

Relations to other collections:

• •Format: targetcollection_relationtype (e.g., gaugingstation_id, plant_spill_to)
• •Type: INTEGER (never NOT NULL)
• •Must have foreign key with ON UPDATE CASCADE ON DELETE CASCADE
• •Self-referential relations use ON UPDATE SET NULL ON DELETE CASCADE

CREATE TABLE Plant(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    label TEXT UNIQUE NOT NULL,
    capacity REAL NOT NULL,
    gaugingstation_id INTEGER,
    plant_spill_to INTEGER,
    FOREIGN KEY(gaugingstation_id) REFERENCES GaugingStation(id) ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY(plant_spill_to) REFERENCES Plant(id) ON UPDATE SET NULL ON DELETE CASCADE
) STRICT;

Vector Attributes

Table naming: COLLECTION_vector_GROUPNAME

Required structure:

• •Column id INTEGER (references parent)
• •Column vector_index INTEGER NOT NULL
• •Primary key: (id, vector_index)
• •Foreign key to parent collection with CASCADE

CREATE TABLE ThermalPlant_vector_costs(
    id INTEGER,
    vector_index INTEGER NOT NULL,
    cost_value REAL NOT NULL,
    FOREIGN KEY (id) REFERENCES ThermalPlant(id) ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY (id, vector_index)
) STRICT;

Set Attributes

Table naming: COLLECTION_set_GROUPNAME

Required structure:

• •Column id INTEGER (references parent)
• •NO primary key
• •UNIQUE constraint on ALL columns combined

CREATE TABLE HydroPlant_set_gaugingstations(
    id INTEGER,
    conversion_factor REAL NOT NULL,
    gaugingstation_id INTEGER,
    FOREIGN KEY (gaugingstation_id) REFERENCES GaugingStation(id) ON UPDATE CASCADE ON DELETE CASCADE,
    UNIQUE (id, conversion_factor, gaugingstation_id)
) STRICT;

Time Series

Table naming: COLLECTION_time_series_GROUPNAME

Required structure:

• •Column id INTEGER (references parent)
• •Column date_time TEXT NOT NULL
• •Primary key: (id, date_time)
• •Foreign key to parent collection with CASCADE

CREATE TABLE Resource_time_series_generation (
    id INTEGER,
    date_time TEXT NOT NULL,
    power_output REAL,
    efficiency REAL,
    FOREIGN KEY(id) REFERENCES Resource(id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY (id, date_time)
) STRICT;

Time Series Files

Table naming: COLLECTION_time_series_files

Structure:

• •Each column stores a file path as TEXT
• •Column names = attribute names

CREATE TABLE Plant_time_series_files (
    generation TEXT,
    cost TEXT
) STRICT;

Quick Reference

Common Mistakes to Avoid

1. •Using plural table names (wrong: Plants, right: Plant)
2. •Forgetting STRICT at the end
3. •Making relation columns NOT NULL
4. •Using ON DELETE SET NULL for non-self-referential relations
5. •Forgetting vector_index in vector tables
6. •Adding primary key to set tables
7. •Using camelCase for attributes (use snake_case)