Medlink - MySQL DB development & integration

[polivagit]

MySQL Medlink Database

All our Medlink applications will connect to a MySQL database where all project data is gathered and managed. For this project, we will be using MySQL Workbench for designing and managing the database.

Database diagram.

• [x] Design EER diagram.

  [Latest] Medlink EER Diagram: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Base%20DB%20Model/medlink_model.png

Reference model:

Generate DB scripts from previous diagram.

• [x] 1. Create script of TABLES based on the designed diagram. If script is autogenerated, check whether is properly created. If not, make some changes.

  [Latest] SQL Script (Schema + Tables): https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Base%20DB%20Model/medlink_corregit.sql

• [x] 2. Import script and create schema with its tables in MySQL Database.

• [x] 3. Create INSERT scripts for:

  • [x] Specialty. SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/specialites/INSERT_specialites.sql

  • [x] Medicine_Category. SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/medicines/INSERT_medicine_category.sql

  • [x] Medicine. SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/medicines/INSERT_medicines.sql

  • [x] Treatment. SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/treatments/INSERT_treatments.sql

  • [x] Treatment_Medicine. SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/treatment_medicine/INSERT_treatment_medicine.sql

  • [x] Unit_Of_Measure. SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/units_of_measure/INSERT_units_of_measure.sql

  • [x] Person (Person). SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/people%20(person)/person/INSERT_person.sql

  • [x] Patient (Person). SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/people%20(person)/patient/INSERT_patient.sql

  • [x] Doctor (Person). SQL Script: https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Guions/people%20(person)/doctor/INSERT_doctor.sql

• [x] 3.1. Merge ALL INSERT scripts into a single script.

  [Latest] SQL Script (INSERT For All Tables): https://github.com/polivagit/Medlink/blob/main/MySQL_Medlink/Base%20DB%20Model/all_inserts.sql

Tables: Guidelines & definitions.

1. Person.

• Base fields: id, nif, first_name, last_name1, last_name2(nullable), birthdate, phone_num, email, gender, address, login_username, login_passwd.
• Save gender as INT, so we can codify the types and use enumType in our apps: male=0, female=1, non-Binary=2 & undefined=3.

2.1. [Person] Patient.

• Extra fields: person_id, height, weight, remarks(nullable), caregiver_id(nullable).
• Height range is between 30-260 cms.
• Weight range is between 2-400 kgs. Float with 2 decimals (0,00).

2.2. [Person] Doctor.

• Extra fields: erson_id, collegiate_id, specialty_id.
• The field collegiate_id cannot be NULL and must have format like: YY-XX-ZZZZZ.
• Doctor users are added to the DB by manual INSERT. There is no need to develop CRUD functionalities for managing the doctor users in our apps.

3. Treatment.

• Base fields: id, name, description, date_start, date_end, doctor_id, observations(?), is_active, treatment_medicines.
• Field observations is MEDIUMTEXT type.
• Field date_start must be between '2000-01-01' & 'CURRENT DATE'.
• Field date_end must be between 'CURRENT DATE' & '3000-01-01'.
• Field is_active must be: 0 = true or 1 = false.

4. Medicine.

• Base fields: id, name, category_id.
• Medicines are added to the DB by manual INSERT. There is no need to develop CRUD functionalities for managing the medicines used in our apps.

5. _[Treatment - Medicine, (N:M)] TreatmentMedicine.

• Base fields: treatment_id, medicine_id, quantity_per_day, total_quantity, unit_of_measure_id.
• Primary key is treatment_id + medicine_id.
• For quantities fields, we use FLOAT but we should round or trucate doses to make it easier for us when we need to calculate quantity_per_day field. We follow this round rules: (>=0,25)&(<0,50) equals 0,25 | (=0,5) equals 0,5 | (>=0,75)&(<=1) equals 1.

6. _Unit_OfMeasure.

• Base fields: id, name, abbreviation.
• Name and Abbreviation fields are UNIQUE.

7. _MedicineCategory.

• Base fields: id, name, desc.
• Medicine categories are added to the DB by manual INSERT. There is no need to develop CRUD functionalities for managing the medicine categories used in our apps.

8. _MedicalSpecialty.

• Base fields: id, name.
• Specialties are added to the DB by manual INSERT. There is no need to develop CRUD functionalities for managing the specialties used in our apps.

Observations.

⚠ For DATE CONSTRAINTS, instead of CHECK we will be using TRIGGERS. With MySQL 8.0.13+ versions, you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. ⚠

Fields affected by this issue:

• pers_birthdate (person)
• trea_date_start (treatment)
• trea_date_end (treatment)

See documentation for more info about this issue:
https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html https://dev.mysql.com/doc/refman/8.0/en/create-table-check-constraints.html