DB Design

[beteivap]

Projects: records the details of the projects Teachers create

• project_id: int, auto_increment, unique, not NULL, PK
• teacher_id: FK from Teachers entity
• class_code_id: FK from ClassCodes entity
• project_code: varchar, unique, not NULL
• title: varchar, not NULL
• description: varchar, not NULL
• category: varchar, not NULL (for ex. Astronomy, Environmental Science, etc…)
• end_date: date, not NULL
• Relationship: a 1:M relationship between Teachers and Projects - a Teacher can create many Projects but each Project belongs to 1 Teacher

Teachers: records the Teacher’s account details

• teacher_id: int, auto_increment, unique, not NULL, PK
• class_code_id: FK from ClassCodes entity
• email: varchar, not NULL
• password: varchar, not NULL
• first_name: varchar, not NULL
• last_name: varchar, not NULL
• school: varchar, not NULL
• date_joined: date, not NULL
• Relationship: a 1:1 relationship between Teachers and ClassCodes - a Teacher is associated with a single class code and each class code belongs to a single Teacher

ClassCodes: records the class codes created for Teachers

• class_code_id: int, auto_increment, unique, not NULL, PK
• code: varchar, unique, not NULL

Observations: records the details of the observations made by students using the field app

• obs_id: int, auto_increment, unique, not NULL, PK
• project_id: FK from Projects entity
• num_obs: int (for Projects that require numerical data)
• text_obs: varchar (for Projects that require text data)
• bool_obs: bool (for Projects that require Yes/No observation)
• obs_date: date, not NULL
• Relationship: a 1:M relationship between Projects and Observations - a Project can have many observations but each observation belongs to a Project

(Brian) Adding some ideas in blue. When thinking about how observations will be entered on the mobile app, and displayed on the admin site would it make sense to separate out independent and dependent variables?

On the admin page, a teacher will need to create a project. I see them selecting the type of the independent variable (number, string, etc.) and the type of the dependent variable (number, string, etc.)

This might be easier to think about with concrete examples Experiment 1: each student will record the hair color of everyone in their family. The independent variable will be hair color (string) and the dependent variable will be the number of people with that hair color(num). The admin page will display the individual observations and a consolidated table of the data for the whole class.

Experiment 2: each student will record their mood at predetermined times throughout the day. The independent variable will be time (date) and the dependent variable will be mood (string).

The benefit of dividing observations into independent and dependent variables is it will make displaying statistics/graphs easier on the admin site, and most experiments will have an independent and dependent variable so I think it more closely models what a citizen science application looks like.

The disadvantage of dividing observations into independent and dependent variables is that there are a lot of different combinations for types of IV and DV. We can mitigate this by coming up with two sample experiments when we demo our app, and ensure the API, Field App, and Admin app can handle these two sample experiments. Another disadvantage is that each row in the database will have unused fields. For example in experiment 1 listed above, the ind_var_num and dep_var_string columns will be null.

I think creating a table for independent variables and dependent variables provides some flexibility. Let’s say down the road we wanted to start accepting lat/long coordinates and photos, where students would take pictures of all birds they see along with coordinates, the coordinates could be the IV and the photo the DV. I don’t like how the number of columns in the IV and DV would keep growing as we add use cases if this were a real product, but I think it will provide enough flexibility for the capstone. Maybe a solution would be to create a tables for each independent variable type - I’m not sure if this overhead will be worth it for the scope of our project though.

Observations

• obs_id: int, auto_increment, unique, not NULL, PK
• project_id: int, FK to projects table
• ind_var - FK to IndependentVariables table
• dep_var - FK to DependentVariables table

IndependentVariables

• id: int, auto_increment, unique, not NULL, PK
• num: optional decimal value if the IV is a decimal
• int: optional int value if the IV is an integer
• string: option string value if the IV is a string
• limited: boolean T/F if the IV has a limited set of possible values e.g. if the IV is hair color and the teacher wants to limit the values to (Black, Brown, Blond, Red) this column would be True.

IndependentVariableValues A table for the allowed values of the independent variable

• id: int, auto_increment, unique, not NULL, PK
• iv_id: int, FK, the independent variable that that this value corresponds to.
• num: an allowed num value for the IV if the IV is a num
• string: an allowed string value for the IV if the IV is a string

DependentVariables

• id: int, auto_increment, unique, not NULL, PK
• num: optional decimal value if the DV is a decimal
• int: optional int value if the DV is an integer
• string: option string value if the DV is a string

[beteivap]

Latest updates to our DB design captured below.

Teachers: records the Teacher’s account details

• teacher_id: int, auto_increment, unique, not NULL, PK
• email: varchar, unique, not NULL
• password: varchar, not NULL [can store this as a hash for security]
• first_name: varchar, not NULL
• last_name: varchar, not NULL
• school: varchar, not NULL
• date_joined: date, not NULL

Projects: records the details of the projects Teachers create

• project_id: int, auto_increment, unique, not NULL, PK
• teacher_id: FK from Teachers entity
• project_code: varchar, unique, not NULL
• title: varchar, not NULL
• description: varchar, not NULL
• prompt: varchar, not NULL
• category: varchar, not NULL (for ex. Astronomy, Environmental Science, etc…)
• end_date: date, not NULL
• iv_info_id: FK to IVInfo table
• dv_info_id: FK to DVInfo table
• Relationship: a 1:M relationship between Teachers and Projects - a Teacher can create many Projects but each Project belongs to 1 Teacher

IVInfo: records the details of the independent variables for a Project

• iv_info_id = int, auto_increment, unique, not NULL, PK
• name = varchar, not NULL
• type = varchar, not NULL (for ex. String, Int, Num, Bool)
• units = varchar
• limited = bool, not NULL (True if limited to only values in AcceptedIV table)

DVInfo: records the details of the dependent variables for a Project

• dv_info_id = int, auto_increment, unique, not NULL, PK
• name = varchar, not NULL
• type = varchar, not NULL (for ex. String, Int, Num, Bool)
• units = varchar

AcceptedIV: records the allowed values for independent variables

• acc_val_id: int, auto_increment, unique, not NULL, PK
• iv_info_id: FK to IVInfo table
• string: varchar
• num_min: int
• num_max: int

Observations: records the details of the observations made by students using the field app

• obs_id: int, auto_increment, unique, not NULL, PK
• project_id: FK from Projects entity
• iv_val_id: FK from IVVals table
• dv_val_id: FK from DVVals table
• obs_date: date, not NULL
• Relationship: a 1:M relationship between Projects and Observations - a Project can have many observations but each observation belongs to a Project

IVVals: records the independent variables values when an observation is made

• iv_val_id: int, auto_increment, unique, not NULL, PK
• int_val: int
• string_val: string
• num_val: float
• bool_val: bool

DVVals: records the dependent variables values when an observation is made

• dv_val_id: int, auto_increment, unique, not NULL, PK
• int_val: int
• string_val: string
• num_val: float
• bool_val: bool

[beteivap]

DB design finalized and captured in Team's Google Drive.