-
-
Notifications
You must be signed in to change notification settings - Fork 23
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Create DB schema for persistent data #124
Comments
https://github.com/codeforboston/home-energy-analysis-tool/blob/summary-outputs/rules-engine/src/rules_engine/pydantic_models.py is where our schema is, you can get a flavor for some of the language we use for the fields in the excel sheet. https://dbdiagram.io/ is a free, neat tool to create database diagrams if that might help folks visualize the proposed schema. |
@alanisaac this is helpful. It looks like we have been working on similar ideas. It will be helpful to coordinate. We can standardize on your names, if preferred. The idea of the schema is just for data to be persisted now. We can add calculated data (for example the number of days between 2 dates) later. |
It's nice that the tool creates SQL too. |
I don't think we need a phone number, or maybe I'm wrong. @stevebreit |
In ENERGY_USE_PERIOD we can probably avoid persisting |
I am not clear what these fields in ENERGY_USE_PERIOD represent:
|
Energy Use Parent Class |
Michelle's Beta 1 (and always): Export data format that can be re-ingested again Notes from Javascript team breakout about where we're at on this issue: Rules engine has it's ideas. Michelle:
|
heat-stack/_heat ~~~~> My page |
When we discussed this last week, I wanted to put together a proposal that maps to what we have in the rules engine. I haven't had a ton of time, but I thought I'd spend part of today's session putting that proposal together. Here it is in DBML. Table case {
id integer [primary key]
home_id integer [ref: > home.id]
created_at timestamp
last_updated_on timestamp
first_name varchar
last_name varchar
// case worker info?
}
Table location {
id integer [primary key]
address varchar
address_line_2 varchar
city varchar
state varchar
zip varchar
country varchar
}
Table home {
id integer [primary key]
location_id integer [ref: > location.id]
// summary inputs
living_area float
fuel_type integer
design_temperature_override float
heating_system_efficiency float
thermostat_set_point float
setback_temperature float
setback_hours_per_day float
// domestic hot water inputs
number_of_occupants integer
estimated_water_heating_efficiency float
stand_by_losses float
}
Table heat_load_analysis {
id integer [primary key]
home_id integer [ref: > home.id]
rules_engine_version varchar
estimated_balance_point float
other_fuel_usage float
average_indoor_temperature float
difference_between_ti_and_tbp float
design_temperature float
whole_home_heat_loss_rate float
standard_deviation_of_heat_loss_rate float
average_heat_load float
maximum_heat_load float
}
Table natural_gas_bill {
id integer [primary key]
case_id integer [ref: > case.id]
provider varchar
}
Table natural_gas_bill_records {
natural_gas_bill_id integer [ref: > natural_gas_bill.id]
period_start_date datetime
period_end_date datetime
usage_therms float
inclusion_override integer
}
Table oil_propane_bill {
id integer [primary key]
case_id integer [ref: > case.id]
provider varchar
preceding_delivery_date datetime
}
Table oil_propane_bill_records {
oil_propane_bill_id integer [ref: > oil_propane_bill.id]
period_end_date datetime
gallons float
inclusion_override boolean
} Differences
|
-- Edited by group on Dec 26 to harmonize HOME and HeatingLoadAnalysis here, with rules engine and spreadsheet
Note: This table represent a single location (ie. a house)
Note: This is the main table for each case, referencing a location associate with the location_id FK
TelephoneNote: This table holds the data for usage csv for each case referencing the case_id FK
The text was updated successfully, but these errors were encountered: