It might be interesting to use composite types (https://blog.crunchydata.com/blog/composite-types) for storing VROOM objects such as time windows. Then, instead of creating a separate table for time windows, it can be used as fields in the same table: time_windows TIME_WINDOW_TYPE[],
DO $$ BEGIN
CREATE TYPE time_window_type AS (
open TIMESTAMP,
close TIMESTAMP
);
EXCEPTION
WHEN duplicate_object THEN null;
END $$;
This can later be extended to the integer array for amounts, skills, capacity, etc, and this would also ensure validation.
The only thing is how to read a non-native Postgres type on the C code. Handling types on an extension is more difficult, so this might not be straightforward.
It might be interesting to use composite types (https://blog.crunchydata.com/blog/composite-types) for storing VROOM objects such as time windows. Then, instead of creating a separate table for time windows, it can be used as fields in the same table:
time_windows TIME_WINDOW_TYPE[],This can later be extended to the integer array for amounts, skills, capacity, etc, and this would also ensure validation.
The only thing is how to read a non-native Postgres type on the C code. Handling types on an extension is more difficult, so this might not be straightforward.