OK, I am attaching a database here, and looking for design advice. I started this out to get the main structure set. Now the user has come to me with a complete list of fields that she wants to have that she can enter useful data into and report on. We discussed everything, and now I am looking for anyone who can tell me how they would add certain of these fields.
This table structure is close to 3rd normal form. (as best as I could do)
I will jump right in with what fields will be added in, please excuse me if there is some sloppiness in my explanations.
TBLActivity:
making contacted staff ( just changing name, no problem)
here is where things get tricky, I am changing the activity types ( adding some listings basically) to include interview scheduled, interviewed, Offer extended, sent to office.
Here is where I am not sure how to handle this. There can be other activities, but these have results that need to be recorded off of them...
If interview scheduled is chosen from the activitytype list, then a date of interview textbox must show. I am not sure how to handle storing this dependant information. (These are the types of questions going on here - conditional drop downs that require more info)
If interviewed is chosen, I need a result combo which would have 2nd interview (needs 2nd interview date), hold ( maybe), or offer extended ( this will also be a direct activitytype)
If offer extended is chosen, either directly from activity type or indirectly from interviewed, then I need a checkbox for accepted ( which needs a textbox for startdate), onhold(no answer?), rejected
My question for all of those where an activity type may now conditionally lead to more info, is how do I best store this info in the table structures in a good and normalized structure? This may make sense by looking at the candidate entry form and looking at the application tab with the activities. Then you can maybe see why I am discussing what needs to be done from an end user stand point. I have the cart before the horse right now, because of the end user giving me these kinds of requests.
Let me move on to the other table changes...
TBLapplications:
solicited is getting deleted, now there will be a table added for applicant source and a dropdown here for how the applicant got to us (web, agency, referral, write-in) (no problem)
now if the appsource has agency or referal chosen I need a text box for the user to enter the specifics. This could be any random person's name and that is the reason for the need of a text box and not a dropdown.
TBLCandidate:
salutation is going to a table with a dropdown ( no problem)
candidate type is going to be a dropdown here now. (see next table for changes)
TBLCandidateTypeInfo ( to become tblAttorneyInfo)
now there will only be info if the candidate type chosen for the candidate is one of the attorney listings ( there are many that would qualify, clerk, associate, partner etc) This tab will only show if one of those options is chosen.
It will have-
attorneytypeinfoid
candid
lawschool (text)
grad date
admitted in state bar ( yes, no, pending)
admit date
Masters degree
notes
(that should be ok, not sure about admitted in state bar one since there are 3 options, almost a yes/no but has a maybe to it)
TBLjobs:
add practice group ( table with lookup to here, same concept as department that job is for)
office (table with lookup here, we have multiple offices)
I think that is it for the start. If anyone can help me to get a good method for how to be store those dependant data factors, that would help a lot. I know this is a lot to throw out there, so please ask as many questions and yell at me if I did horrible explaining!
This table structure is close to 3rd normal form. (as best as I could do)
I will jump right in with what fields will be added in, please excuse me if there is some sloppiness in my explanations.
TBLActivity:
making contacted staff ( just changing name, no problem)
here is where things get tricky, I am changing the activity types ( adding some listings basically) to include interview scheduled, interviewed, Offer extended, sent to office.
Here is where I am not sure how to handle this. There can be other activities, but these have results that need to be recorded off of them...
If interview scheduled is chosen from the activitytype list, then a date of interview textbox must show. I am not sure how to handle storing this dependant information. (These are the types of questions going on here - conditional drop downs that require more info)
If interviewed is chosen, I need a result combo which would have 2nd interview (needs 2nd interview date), hold ( maybe), or offer extended ( this will also be a direct activitytype)
If offer extended is chosen, either directly from activity type or indirectly from interviewed, then I need a checkbox for accepted ( which needs a textbox for startdate), onhold(no answer?), rejected
My question for all of those where an activity type may now conditionally lead to more info, is how do I best store this info in the table structures in a good and normalized structure? This may make sense by looking at the candidate entry form and looking at the application tab with the activities. Then you can maybe see why I am discussing what needs to be done from an end user stand point. I have the cart before the horse right now, because of the end user giving me these kinds of requests.
Let me move on to the other table changes...
TBLapplications:
solicited is getting deleted, now there will be a table added for applicant source and a dropdown here for how the applicant got to us (web, agency, referral, write-in) (no problem)
now if the appsource has agency or referal chosen I need a text box for the user to enter the specifics. This could be any random person's name and that is the reason for the need of a text box and not a dropdown.
TBLCandidate:
salutation is going to a table with a dropdown ( no problem)
candidate type is going to be a dropdown here now. (see next table for changes)
TBLCandidateTypeInfo ( to become tblAttorneyInfo)
now there will only be info if the candidate type chosen for the candidate is one of the attorney listings ( there are many that would qualify, clerk, associate, partner etc) This tab will only show if one of those options is chosen.
It will have-
attorneytypeinfoid
candid
lawschool (text)
grad date
admitted in state bar ( yes, no, pending)
admit date
Masters degree
notes
(that should be ok, not sure about admitted in state bar one since there are 3 options, almost a yes/no but has a maybe to it)
TBLjobs:
add practice group ( table with lookup to here, same concept as department that job is for)
office (table with lookup here, we have multiple offices)
I think that is it for the start. If anyone can help me to get a good method for how to be store those dependant data factors, that would help a lot. I know this is a lot to throw out there, so please ask as many questions and yell at me if I did horrible explaining!
