Search code examples
mysqlsqldatabase-designdiagramclass-diagram

MySQL Access Control List

I am trying to implement a site where patients can access medical records. My problem is where the clinicians have access to the records. So they should only be able to view the patient's record if they are on an Access Control List which states which doctors can view the patient's record. The question that I have is what type of relationship will the clinicians have to the access control list; would it be many to one, many to many, etc.? Also I am not entirely sure about the foreign keys.

This is my image of what I think it would look like, any help would be awesome. enter image description here

Solution

  • Issues:

    1. I don't see why you have both patient and medical records on the access list. (if 1:1 patient medical record)
    2. I don't see why you have accessList info on Clinician. This means that a clinician can only have access to 1 access list? seems wrong.

    Question:

    • Are you trying to setup the data so that you have ACL groups you assign a patient to a "group" and all Clinician's part of that group then have access so you don't have to manage the individual Patient Clinician associations? If so we need a ACL_GROUP table and Clinician's should be assigned to a group; or you need a Hierarchy within your ACL table.

    How I'd likely setup the data with the information to date:

    Patient

    • idPatient (PK)

    Medicalrecords

    • idMedicalrecord (PK)
    • Patient_idPatient (FK to patient)

    Clinician

    • idClinician (PK)

    AccessList

    • idAccessList (PK)
    • AccessListName (Group name or patient Name)
    • Patient_idPatient (FK to patient) (COMPOSITE UNIQUE INDEX1)
    • Clinician_idClinician (Fk to Clinician) (COMPOSITE UNIQUE INDEX1)
    • IdAccessList_Parent (Maybe... FK to AccessList_IDAccessList) used to manage "security groups")

    This allows the following:

    1. A patient record could have 1:M Medical records
    2. A medical record associates to 1 and only 1 patient
    3. A clinician may have access to a patient and all their medical history; but not specific medical records if multiple. If you need to limit access to certain patient medical information then instead of having the accessList join to Patient, have it join to MedicalRecords; thereby allowing to you to limit to specific medical inforamtion; or keep both allowing access to the whole patient; or specific medical records if multiple exist.