none
Database Design RRS feed

  • Question

  • Hello. 

    I am designing a database for a program that helps high school students get into college. We want a database to track and report on any student who applies (they may or may not have been recruited) and any student who is recruited (they may or may not apply) to the program.

    Furthermore, we use a third party web site to collect applications. This application data can be easily imported into Access. And in regards to recruitment, we can use a simple table to record basic contact information and contact attempts. 


    How should I design the database to accommodate applicants and recruits? My idea right now is that there would be two tables. One table for the application info that gets imported (this table would be deleted and then re-created with each iteration of the application import file--because it's cumulative). The second table would be a "main table" that has all of the recruits (who aren't applicants yet) and applicants (who aren't current recruits or were formerly recruits). Everyone would have a "program id", but only the applicants would have an "application id". So, when a new round of application data is imported into the database, someone would need to manually check to see if the recruits in the main table are in the new application table. If the recruit, say John Doe, is in the application table, then a database user would enter the application id for John Doe into the "main table", which would link the two tables for John Doe. Similarly, a database user would need to check to make sure that a recruit hasn't already applied. Is there a simpler, more fool-proof design? 

    Thanks in advance!

    -Darryl 



    Tuesday, June 28, 2016 3:37 PM

Answers

  • The hundred or so more fields will all be in separate tables anyway …

    With a status field, you can easily view all the students who are of a particular status with a query.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, June 29, 2016 10:01 PM

All replies

  • Hi Darry,

    >>Everyone would have a "program id", but only the applicants would have an "application id".

    What is “application id”? Is it a field from third party web site or an auto generated by application table? To avoid delete and re-created application information table, I would suggest you create a temporary table for importing application information, after importing information, check the records in tem table whether it is exist in application table, if not, insert it. Next time, delete tem table and insert the records which are not exist in application table.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, June 29, 2016 2:02 AM
  • Thanks for your response Edward! And yes, the application Id is generated from the third party web site. I like your idea of a temp table--that's a safer way to handle the data.

    I'm thinking of a new way to design the database. The non-negotiable data are those that are admitted into the program, and for us we only want basic stats on the other two groups: the recruits and applicants (not admitted) So, I am thinking of four tables: temp application table, recruits table, applicants table, and an admitted students table. Originally, I wanted the tables to be mutually exclusive, but that's not really necessary for us and may be too much work to implement. So, the recruits table will just be for tracking recruits and a recruits form will filter out students who have been admitted or declined participation. The applicants table will have all of the downloaded info from the third party web site. New applicant info will be imported into the temp application table first and then new applicants will be added to the applicants table. Lastly, If an applicant has been admitted into the program, a new record will be created in the admitted students table.

    Thoughts anyone?


    Darryl

    Wednesday, June 29, 2016 3:09 PM
  • It seems to me that the basic data about the people involved don’t change much, so moving them from one table to another is a bit of work. What does change is their status: recruit, admitted, applied, etc., which is a smaller table (5 or so rows). That’s really the only thing that needs to change if I understand correctly.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, June 29, 2016 6:37 PM
  • Thanks Peter for your response. You're right that the basic info for the students doesn't change, but once a student is admitted, the number of variables for that student (e.g., attendance, grades, goals, etc.) grows by the hundreds, so I didn't want recruits (we'll only need 10 or so variables for them) in the same table with admitted students. And I'm not sure what you mean by a smaller table? Does that table combine the others that I described above?

    Darryl

    Wednesday, June 29, 2016 7:00 PM
  • The hundred or so more fields will all be in separate tables anyway …

    With a status field, you can easily view all the students who are of a particular status with a query.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Wednesday, June 29, 2016 10:01 PM