I posted a question yesterday using the example that I wanted to sort specific tasks by farmer - I've since been informed that I don't need to be so vague as to what I'm trying to accomplish - sorry Ajax and Micron!
So what I'm really trying to do is create a catalog of the specific orthopedic problems that the orthopedic surgeons in our region (and neighbouring regions and cities) will and won't do. For example, we have many surgeons who will do knees (Primary Problem), however some won't accept knee referrals with arthritis, others won't do ligament tears, some require x-rays with all referrals, whereas others will accept with an MRI only as long as there isn't any signs of arthritis on the MRI... It gets even more complicated because some of these surgeons will also treat ankles, hips, shoulders, elbows, etc, and each of those primary problems have many "sub-problems" or "secondary problems" which come with their own nuances.
On top of that, we also have Triage Assessment Clinics who specialize in just Hip and Knee arthritis, or just shoulders, or just feet, etc. They also accept orthopedic referrals, and they too have their own requirements.
My end goal is to be able to create queries that pull up which surgeons do the specific sub-problem, and by city or region (and if I'm not asking too much, I would love it if I could also create a relationship between the surgeon, sub-problem, and that surgeon's specific requirements for that sub-problem)
Example: "Knee meniscal tears with arthritis"
- Dr. A. Smith; requirements - x-ray done within last 6 months
- Dr. B. Jones; requirements - x-ray done within last 9 months
- Dr. C. Martin; requirements - unknown
I think I understand what Micron suggested yesterday about creating a junction table, but wondering if it's wise to connect more than 2 tables to the junction? In my picture below, you can see I haven't connected the Triage Assessment Clinic table to the junction table, primary or secondary problems yet. Perhaps the Orthos and Triage Clinics should be in one table? Or the Triage Assessment Clinics should get their own junction table with the secondary problems?
And right now I have imaging requirements (i.e. xrays, MRIs, ultrasounds, etc) as a combo box under Secondary Problems... I'm not sure if that's ideal or if it should also be it's own table.
Any suggestions as to how I have my relationships arranged would be most appreciated!