pnnl / building-energy-standards-data

Database of building energy standards data for building energy simulation.
Other
8 stars 3 forks source link

Enforce `name` to be UNIQUE in Schedule Table schema #98

Open jiangyilin123 opened 2 months ago

jiangyilin123 commented 2 months ago

The current schedule table allows duplicates in the name field. However, if a new table wants to reference the name of the schedule table as a foreign key, it will fail to do so because the "name" is not unique or a primary key.

The following is one example: in this PR, the author want to reference name in support_schedules as the foreign key in support_occupant_physical_characteristics table.

       Thank you for the comment. I will follow Weili's suggestion at this stage. Originally, the `support_occupant_physical_characteristics` has the following code, which sets `name` in `support_schedules` as the foreign keys. However, this setting cannot pass the test because `name` is not a unique value. Do you have any suggestions to do this right?  
CREATE_PHYSICAL_CHAR_TABLE = """
CREATE TABLE IF NOT EXISTS support_occupant_physical_characteristics (
    id INTEGER PRIMARY KEY,
    physical_characteristic_name TEXT NOT NULL,
    schedule_activity_level TEXT NOT NULL,
    schedule_clothing_insulation TEXT NOT NULL,
    schedule_air_velocity TEXT NOT NULL,
    work_efficiency NUMERIC,
    co2_generation NUMERIC,
    co2_generation_units TEXT,
    annotation TEXT,
    FOREIGN KEY(schedule_activity_level) REFERENCES support_schedules(name),
    FOREIGN KEY(schedule_clothing_insulation) REFERENCES support_schedules(name),
    FOREIGN KEY(schedule_air_velocity) REFERENCES support_schedules(name)
);

_Originally posted by @jiangyilin123 in https://github.com/pnnl/building-energy-standards-data/pull/97#discussion_r1777587863_