Best way to handle this?

[Michael Micelli]

Greetings,

I'm not sure how to approach this problem.

I have one table to hold applicant information (fName,
lName, etc...)

I have another table that has the names of forms that will
need to be completed to process the applicant.

I have another table that has applicant type. The type
decides which forms will need to be filled out.

I need to reflect which forms have been completed for each
applicant depending on type. Where would be the best place
to store this information?

Thanks,
Michael Micelli

 

[Tim Ferguson]

I need to reflect which forms have been completed for each
applicant depending on type. Where would be the best place
to store this information?

It depends... (you'll hear a lot of this in db design world!)

One solution would be to create a table called FormsFilled:

*ApplicantID FK references Applicants
*FormCode FK references Forms
DateFilled date/time
FilledInByWhom FK references FormFillingPersonnel

If the forms-to-be-filled in for particular application types rarely
change, then you could simply make a new FormsFilled record every time a
form is filled in. To find which forms have not yet been done, you would
run a query that joins the Applicant to the ApplicationType to the
FormsRequired to the FormsFilled.

On the other hand, if the forms requirements do change, or are sometimes
waived manually (manager's discretion kind of thing) then it is probably
better to have a FormsNeeded table that is filled in at the time of the
application, leaving the DateFilled fields blank. That way, outstanding
records can be found by searching for WHERE DateFilled IS NULL.

You have not really said what you want to know about this formfilling.
Outstanding ones or completed ones? Timing or numbers? What about the stuff
on the forms themselves, or are we talking about just the fact of the form
being filled? Or migrating over time?

Hope that helps a bit

Tim F

 

[Michael Micelli]

Thanks Tim

-----Original Message-----


It depends... (you'll hear a lot of this in db design world!)

One solution would be to create a table called FormsFilled:

*ApplicantID FK references Applicants
*FormCode FK references Forms
DateFilled date/time
FilledInByWhom FK references FormFillingPersonnel

If the forms-to-be-filled in for particular application types rarely
change, then you could simply make a new FormsFilled record every time a
form is filled in. To find which forms have not yet been done, you would
run a query that joins the Applicant to the ApplicationType to the
FormsRequired to the FormsFilled.

On the other hand, if the forms requirements do change, or are sometimes
waived manually (manager's discretion kind of thing) then it is probably
better to have a FormsNeeded table that is filled in at the time of the
application, leaving the DateFilled fields blank. That way, outstanding
records can be found by searching for WHERE DateFilled IS NULL.

You have not really said what you want to know about this formfilling.
Outstanding ones or completed ones? Timing or numbers? What about the stuff
on the forms themselves, or are we talking about just the fact of the form
being filled? Or migrating over time?

Hope that helps a bit

Tim F

.