How can I use multiple defaults in a table?
Posted on 2009-02-24
I'm trying to do something which is very tricky to explain but hopefully someone will understand and be able to shine a light on the a situation for me.
I am creating a database of medical professionals, I have 4 tables which are relevant to this problem.
Table1 -Contains all personal info for the person (EG FirstName, Surname etc) PK = PersonID
Table 2 -Contains a composite of foreign keys which colate all the detail needed for a persons occupation.
PK = PersonOccupationID, FK=PersonID, FK=OccupationID, FK=GradeTypeID
Table 3 -Contains a list of various generic Occupations a person in medicine can do (Eg Doctor, Nurse etc) PK=OccupationID
Table 4 -Contains a list of the different variations each of Table 3's Occupations can be. (Eg Doctor, can be GP or Hospital, Nurse, can be Community or Hospital) PK=GradeTypeID, FK=OccupationID
So you might have noticed that the OccupationID exists in the GradeType table. I have done this because I was hoping that this structure would mean that if "nurse" was selected from the Occupation table then this would mean that in the GradeType table, only the GradeType fields with the relevant OccupationID for Nurse could be selected.
Basically I want to ensure that if Nurse is selected in Occupation then only the options relevant to that can be selected in GradeType. ie, A Nurse can only be Community, Hopital, MacMillan etc, and not GP which is only relevant to Doctor in Occupations.
Does that make sense?