Medical database conceptualisation.
Here's my problem.
I'm trying to help someone out on an altruistic medical database project and this is the problem..
A patient has either one or potentially multiple sites on (or in) their person which is of possible medical concern. Let's say there are 10 unique sites (for example, knee, elbow, jaw, arm, finger, ovaries, etc etc...), yet a patient statistically will only ever display issues with a max of three.
For each of the 10 possible sites, there are up to 20 unique (to each site of the body) descriptive items of information. So a patient might have up to 3 sites of concern, and 20 things needing input relating to each site.
My approach so far has been to:
A) obviously create patient details table.. unique id, standard stuff..
B) create a new table for each 'site' populated with fields relevant to that site (so 10 tables with the 20 or so relevant fields for each).
C) create a 'site data' table which is linked (relevant) to the patient whereby there are dropdown lists....
here's where I'm stuck...
Is it possible to say select "elbow" from a drop down list created from a value list from possible sites, and have the relevant "elbow" data (the 20 or so input fields) table pop up in a portal.. so inputs can be made specific to the elbow (and patient) yet not displaying all 10 possible sites on the original ?
I'm sure there's a simple solution... but damn it.. just can't quite conceptualise the whole thing.
Having said that.. I believe that's the problem.. newbie conceptualisation.
The idea here is to provide the user (doctors) with 3 possible 'sites' to select, then conditionally, upon selection, easily input relevant data relevant to that site without cluttering the process?
Any advice would be greatly appreciated. Even the general direction. :)