I am new to database building and have been fighting with a certain query problem for a couple of days now. All I need really is how this kind of Problem is called, but I would appreciate a longer answer!
Let's say I have a couple of
Clinics that have some specs such as a
Technology that belongs to a
TechnologyCategory and has a
Rating in this category,
Specialisation that belongs to a
SpecialisationCategory and has a Rating, and so on for about 3-4 specs. Now I also have a list of
Treatments that belong to a
TreatmentCategory and those
Treatments have each a
Requirement with one or more entries of the specs that a clinic can have. My goal is to get all Clinics that can do a certain Treatment and all Treatments that one Clinic can do.
So far I came up with this
For my TreatmentRequirement I would create an Index
so that I can take a Clinic, go through all my Requirements and check them against my ClinicSpecID. The Rating in the SpecCategory of the Clinic must then be higher then the rating of the Requirement (5 in the Index) in order to make it into the final result.
Or in pseudo Code
for every Clinic for every [TreatmentCategoryID, TreatmentID] for every [SpecCategory, LevelOfConfidence] if Clinic.Rating(TreatmentCategoryID) < SpecRating suitable = 0 break else suitable = LevelOfConfidence if suitable add [TreatmentCategory,TreatmentID, suitable] to Clinic.AllowedTreatments
now this is a 5 time nested loop and my question is if someone has an Idea how to do things more efficient, or if this kind of Problem has a name so that I can go on researching. Please tell me if something is not clear. Thank you in advance. Also If somebody knows what title would describe this problem better, I would appreciate it.