Database Scheme

Database_Scheme_Pallicare

• This database scheme was created with the software "DBSchema", which provides an graphical user interface for designing a database scheme for MariaDB as well as SQLite. Afterwards the scheme was exported as SQL-Script, which can be used to implement the database-scheme in the web-server and the local database of the android-application.
• To get the most recent version of the db-scheme scroll to the bottom of the wiki-page \ below the image of the db-scheme there is a link provided to download the sql-script

Description	This database scheme is a template for saving data in a normalised, structured way to ensure integrity for the data stored on the local device as well as the web-server
Primary author	Patrick Höfner
External libraries used	https://dbschema.com/

DB-Scheme-v1

Tables	Each table represents one object with several attributes
user	represents one specific user of the application, the user-type is set by a Foreign-Key relationship to the user_type-table
user_type	represents one specific user-type (patient, doctor, next-of-kin)
permission	represents the permission a user will have to modify patient-data
	the Foreign-Key patient_id is related to a specific patient (user where user_type = patient) whose data should be accessed
	the Foreign-key requester_id is related to a specific user (user_type = patient, doctor, NoK) who requests a permission to access patient data
questionnaire	represents the midos-questionnaire with all questions
other_suffer	represents other-suffers which are not included by default in the midos questionnaire
	every other suffer is related to a specific questionnaire by a ForeignKey to the questionnaire_table
device	represents one device which is used for measuring data automatically
	a device is related to a specific user by a ForeignKey to the user_table
device_type	represents the type of a specific device (e.g. scale)
measurement_scale	represents one measurement which is gathered by a scale (weight, fat, muscle, water)
	one measurement is related to one specific patient by a ForeignKey to the user_table
measurement_bloodpressure	represents one measurement for bloodpressure
	one measurement is related to one specific patient by a ForeignKey to the user_table
Relationships	user_types -> user: 1:n
	user -> permission : 1:n (2x)
	user -> questionnaire : 1:n
	questionnaire_types -> questionnaire 1:n
	user -> measurement_scale: 1:n
	user -> measurement_bloodpressure: 1:n
	device -> user: 1:n,
	device_type -> device: 1:n

dbSchemeScript_v1.sql

DB-Scheme-v2

Development history	changes compared to DB-Scheme-v1
table permission	the access-level of data is set by a ForeignKey to a new table permission_type
added table permission_type	represents the access-level which is granted by a permission (0=removed 1=denied 2=requested 3=granted)
Relationships	permission_types -> permission: 1:n (3x)
Constraints	permission_types (0 = removed, 1 = denied, 2 = requested, 3 = granted) -> referential integrity (Constraint-type)
	questionnaire (0=nothing, 1=slightly, 2=medium, 3=strong) -> check-constraint (Constraint-type)
	information about sql-constraints -> https://www.w3schools.com/sql/sql_constraints.asp

further attributes that should be constrained from application-site

• specific "user_types" (NoK and Doctor) never should be able to get "permission_write = true" -> means if the "requester_id" is NoK or doctor only relations with "permission_write = false" are allowed -> check constraint with subquery is not supported -> trigger-function would probably a solution, but are really uncommon -> constraint needs to be set from application-site

• pre-defined tables "user_type" and "device_category" should never be touched after creation

• specific "user_types" (NoK and Doctor) should not have database_entries for "measurements", "questionnaire" and "devices" data -> means for all "patient_id" fields only relations with "user_type = patient" are allowed

superuser

• should be able to read, change, add and delete data from the database
• can be realised with another user_type "admin" which has all permissions and is related to every patient

dbSchemeScript_v2.sql

Comment by Daniel: should probably add another table in the same way as the measurement tables that includes threasholds for each patient as well as the health_status and midos_status that gets updated whenever a new measurement gets added. Onyl one of these tables should exist per user