signal nested records table structure

This table is designed to hold the schedule of plans for each physical semaphore.

The purpose of this table to map a phasing scheme (value_phasing) and a timing structure (value_timing) for each signal (object_id) for each time period extending from value_start to value_end

For each object_id, there should be records covering the entire time from 0:00 to 24:00

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
object_id	INTEGER	NO		Signal(object_id)	signal object (Signal table) that it refers to
index	INTEGER	NO			index within the signal
value_start	REAL	YES	0		signal start time (HH:MM)
value_end	REAL	YES	0		signal end time (HH:MM)
value_timing	INTEGER	NO	0		which timing record (Timing table) to be applied
value_phasing	INTEGER	NO	0		which phasing configuration (Phase table) to be applied

(* - Primary key)

The SQL statement for table and index creation is below.

create TABLE IF NOT EXISTS "Signal_Nested_Records" (
    "object_id"      INTEGER NOT NULL,
    "index"          INTEGER NOT NULL,
    "value_start"    REAL             DEFAULT 0,
    "value_end"      REAL             DEFAULT 0,
    "value_timing"   INTEGER NOT NULL DEFAULT 0,
    "value_phasing"  INTEGER NOT NULL DEFAULT 0,

    CONSTRAINT "object_id_fk" FOREIGN KEY("object_id") REFERENCES "Signal"("signal") ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS "Signal_nested_records_index_i" ON "Signal_nested_records" ("index");

CREATE INDEX IF NOT EXISTS "Signal_nested_records_object_id_i" ON "Signal_nested_records" ("object_id");

CREATE UNIQUE INDEX IF NOT EXISTS Signal_nested_records_unique  ON Signal_nested_records (object_id, "index", value_start, value_end);