search

jackrk / EnergyYardstick

This tool will be hosted separately from the City of Ames website. The city plans to have our tool available for users directly from their main website.
0 stars 0 forks source link

Database - Create Initial Database Queries #15

Open jackrk opened 10 years ago

jackrk commented 10 years ago

Develop the database queries that we'll need.

  1. get 12/24/(possibly 36) months of data for the user (by address, or whatever you think is best)
  2. get house information (sq ft, floor-plan type, neighborhood, age, whatever data is relevant)
  3. get average usage for comparison to various categories (we can discuss this, but I think for now we should have one query that factors in a range of sq. ft, age, the specific neighboorhood and floor-plan type, and returns an average monthly usage for that data set. NOTE: If those queries are too slow (I doubt they would be since sql is usually very fast unless you do something with 'unique' values), then we might have to maintain a table of pre-calculated values with the averages.
  4. Any other queries you think would be useful.
jackrk commented 10 years ago

I think the best design will be to have stored procedures inside our database. If you aren't familiar, you can write procedures in sql that are more or less just queries, and then call those procedures from outside. That way our php controller can just call the stored procedure in one line and if we need to change the procedures there is just one instance to change.

Look up the syntax and stuff for a procedure and implement them based on the requirements above.