Database
This page gives an overview of all the tables in the ClimApp database.
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 code (and related classes that implement URL)
- 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
This section includes all tables in the database their relations to other tables is shown above in the entity relationship diagram.
- 🔑 - primary key
- 🎤 - foreign key
- ❗ - important comment
| 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 |
| 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 | boolean | ❗ 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. |
| 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 |
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 |
