Database

This page gives an overview of all the tables in the ClimApp database.

Changes

When changing database structure or API remember the dependencies to the PHP API and the code of the devices using it.

Code:

• Database (on server)
• Java API string (and related classes that implement URL, any or all of these)
• PHP code (on server)
  • ClimAppAPI.php (create new version)
  • DbOperation.php

Documentation:

• Database (this)
  • Generate new ER diagram and add in Database (here)
• Database API
• Visio
  • API calls and parameters
• Database update triggers

Entity Relationship Diagram

Generating ER diagram in PHPMyAdmin

• Log in to PHPMyAdmin
• In the side bar to the left, choose climapp (see image below if something is unclear)
• In the view that open, click on Designer
• Below the database overview is shown, rearrange the table and choose Save page as to save an image
• Add the file to the Coneptual drawings folder and change the link to the file on this page to match (or overwrite to minimize work).

*

Tables

This section includes all tables in the database their relations to other tables is shown above in the entity relationship diagram.

Overview

• 🔑 - primary key
• 🎤 - foreign key
• ❗ - important comment

user

Field name	Type	Description
_id 🔑	Varchar(50)	Global user id generated from device ID on Android
age	int(2)
gender	int(1)	0 - Male, 1 - Female
height	decimal(3,2)	Stored in meters
weight	decimal(5,2)	Stored in kg
unit	int(1)	0 - SI units, 1 - US 2 - UK imperial
created_on	timestamp	Time when user first used the app
last_updated	timestamp	Last time user changed a user setting

weather

Field name	Type	Description
_id 🔑 🎤	Varchar(50)	Global user id generated from device ID on Android from user table
created_on 🔑	timestamp
longitude	decimal(11,8)
latitude	decimal(11,8)
city	Varchar(30)	❗ Not implemented yet.
temperature	decimal(5,2)	Stored in the database in Kelvin.
wind_speed	decimal(6,3)	m/s
humidity	decimal(3,2)	Value between 0 and 1
cloudiness	decimal(3,2)	Value between 0 and 1
acclimatization	int(1)	0 - not acclimatized, 1 - acclimatized ❗ Not implemented yet.
activity_level	Text	Values: "low", "very low" "medium" "high" "very high"
temp_min	decimal(5,2)	Calculated minimal temperature. Stored in the database in Kelvin.
temp_max	decimal(5,2)	Calculated minimal temperature. Stored in the database in Kelvin.

feedback

Field name	Type	Description
user_id 🔑	Varchar(50)	Global user id generated from device ID on Android from user table
created_on 🔑	timestamp	Server time when user access app first time
question_combo_id 🎤	int	The reference to the question id in feedback_questions
rating1	int(1)	Rating from 1-5
rating2	int(1)	Rating from 1-5
rating3	int(1)	Rating from 1-5
txt	Text	Additional (optional) free-text comment from user

feedback_questions

Table of the different question combination the user can be presented with. 3 is standard, but the number of question can be less.

Field name	Type	Description
id 🔑 🎤	int(100)	The ID of the question combination
num_questions	int(1)
q1	Text	1st question to ask user
q2	Text	2nd question to ask user
q3	Text	3rd question to ask user