FMP DB

Intricacies of the FileMaker Pro Database

Schema Images

Main Tables and Descriptions

TODO: fill descriptions for all these tables

• clinics

  • DESC: Every row of this table describes a clinic name and who created/modified this row
  • PK: pk_clinic_id
  • FKs:

• patients

  • DESC: Every row in this table is a patient that is being treated by CHI
  • PK: pk_patient_id
  • FKs: fk_geoname_id

• visits

  • DESC: Every row in this table is a visit made by a patient to a CHI clinic
  • PK: pk_visit_id
  • FKs: fk_clinic_id, fk_patient_id

• diagnoses

  • DESC: Every row in this table is a common diagnoses given by CHI
  • PK: pk_diagnoses_id
  • FKs:

NOTE: there is also a pk_diagnosis_key that is a unique text field with an enum-like equivalent of the diagnosis name. THIS IS NOT THE PRIMARY KEY THOUGH!

• diagnoses_made

  • DESC: This is a join table that connects a diagnosis and the visit that it happened in
  • PK: (fk_visit_id, fk_diagnosis_key)
  • FKs: fk_visit_id, fk_diagnosis_key

• meds

  • DESC: Every row in this table is a medication distributed by CHI during their visits
  • PK: pk_med_id
  • FKs:

NOTE: there is also a pk_med_key that is a unique text field with an enum-like equivalent of the medication name. THIS IS NOT THE PRIMARY KEY THOUGH!

• meds_dispensed
  • DESC: This is a join table that links visits to the distributed medications
  • PK: (fk_visit_id, fk_med_key)
  • FKs:

Note: fk_med_key is not the primary key of the meds table. fk_med_key is a string-like enum that corresponds to one entry in the meds table at meds(pk_med_key)

• geonames

  • DESC: Each row corresponds to a location, GPS coordinates, and the timezone it is in
  • PK: pk_geoname_id
  • FKs:

• settings

  • DESC: This table connects to other tables in the FMP database but we are unsure of how it works in the schema
  • PK: pk_settings_id
  • FKs:

• globals

  • DESC: This table holds global variables
  • PK:
  • FKs:

DB Diagram