Database structure; working like a flowchart?
Posted on 2009-02-24
I'm creating a database of medical professionals and patients. Obviously the information for each of these person types varies quite a lot. For the professionals I need to record their occupation and place of work, while for the patients I also need their occupation but that is much more varied than the possible occupations of those working in medicine, and also need to record their conditions and medications.
I have one central table (Table 1) upon which I think this will all rest. Table 1 records all personal info like name, contact details, home address, etc. Is there a way to indicate in this table that if a field is filled with "professional" it will take you to the various tables which need to be filled for that professional ie Type of Doctor, Department in the relevant tables . And if you are a "patient" then you are directed to the tables on medical conditions and medications.
Is this possible? I get confused between triggers, procedures, and views, and I'm starting to think that this structure may not be the best way as I am envisaging it working like a flowchart.