Relationship Query

[Rupert Snyman]

Hi, 

 

First Post here, look forward to meeting you all.

 

I'm doing an electronic Medical Record for my own use. I'm a bit stuck on my relationship graph. Patient_UUID is my foreign key in tables used to create x-ray forms, prescriptions etc. I wan't to expand to make my database multi doctor and have preferences for each Doctor. e.g. address, templates, macros, letterhead etc

 

Pref_Doctors is my doctors table with a Primary Key of Doctor_ID. I obviously need to link this to Prescriptions to allow the doctors address etc to load on the script as a merge field.

 

My question is do I need to do a new TO of Pref_Doctors for each item I wish to link? e.g. operations, medical certificates and Xray forms? Could get messy really quick.

 

Thanks

Rupert

[jbante]

Another question worth considering: if you want prescriptions, operations, medical certificates and x-ray forms to all have a similar relationship to Pref_Doctors, does it actually make more sense to model each of those as different types of the same entity rather than different entities, i.e., for each of those to be represented by records in the same table with different values in a "type" field? Sure, some of those will have different attributes than the others, but that's what EAV models are for.

[Rupert Snyman]

Thanks Jeremy, I started by minimizing the number if entities but complexity grew as a result.

I grouped all my contacts together eg doctors , insurance companies, solicitors, return to work coordinators, the benefits were diminished the further I went as the differences between groups became more pronounced.

Each of those entities, X-ray forms, prescriptions etc are only similar in their relationships otherwise they are pretty different with no shared layouts.

I enjoyed your work on value lists on GitHub, thanks,

Cheers

Rupert

[Manjit Behera]

Hi Rupert,

 

To create multi-record relation between Doctor, Patient, Prescriptions etc. you need to use some intermediate (transactional) table to link both the tables so that 1 Doctor can link to multiple patients as well as single patient can link with multiple Doctors. See following example:

 

 

Doctor Table

----------------

pk_DoctorID

DoctorName

 

 

Patient Table

----------------

pk_PatientID

PatientName

 

 

DoctorPatient table (Transactional table)

-----------------------

pk_SerialID

fk_DoctorID

fk_PatientID

 

 

 

Thanks,

Manjit

[Rupert Snyman]

Thanks Manjit, I already have a join table for that purpose to manage contacts. I think I'd need a multi purpose join table that creates records each time a prescription or form is written, recording author (doctor) and patient. This would be related to each of the prescription/ form tables and allow a doctor address lookup/ merge field for form printing.

Alternatively I could just have multiple Doctor TOs as I mentioned.

Thanks for the help

Rupert