I'm seeking some guidance/ideas on the best way to handle the on-going addition of new data.
I have thousands of records containing recruit contact information that we may get on our own, or may be sent to us from our clients. If a list of recruits are sent to us from a client, they can only be contacted for specific projects which we refer to as a "closed list." However, at times we receive a recruit that we already have on our own recruit list, or possibly we have on a seperate closed list from a different client. We need to be able to track all instances. If they are ours, client a, client b, etc.
Currently, I have a "Recruit" table that assigns a system generated unique number when someone is added. The "Recruit" table has a one to many relationship with the table "ClosedList". Lists of recruits are sometimes added using imports (which I perform), and sometimes recruits are added by support staff one by one. I'm looking for the best way to automate the following whether it's done using an import or one-by-one using a support staff interface/layout:
1) Enter/import a new recruit(s) (name, email, phone, etc.). Only unique email addresses are allowed but it is possible to override for special circumstances.
2) Automatically find any email address, first name, last name that already exist in the DB
a) If the recruit already exists
1) Flag the recruit that they have at least 1 record in ClosedList
2) Add the UniqueID to the ClosedList and the ListName (this could be manually entered for one-by-one entry, or would already exist in a xlsx being imported)
b) If the recruit does NOT already exist
1) Add all appropriate fields to the Recruit table, including a ClosedList flag
2) Create a record in the ClosedList table containing the UniqueID the system generated as the recruit is added as well as the ListName (manually entered for one-by-one entry, or automatically from the xlsx import)
I'm not looking for a specific answer necessarily, but direction on good ways to go about this and things to keep in mind that may cause issues.