Am i violating any NF RULE on my database design?

Good day!

I am a newbie on creating database.开发者_开发技巧.. I need to create a db for my recruitment web application.

My database schema is as follows:

NOTE: I included the applicant_id on other tables... e.g. exam, interview, exam type.

Am i violating any normalization rule? If i do, what do you recommend to improve my design? Thank you

---

Overall looks good. A few minor points to consider:

• Interviewer is also a person. You will need to use program logic to prevent different / misspellings.
• The longest real life email address I've seen was 62 characters.
• In exam you use the reserved word date for a column name
• (subjective) I would rename applicant_date to applied_at
• I don't see a postal / zip code for the applicant
• All result columns are VARCHAR(4). If they use the same values, can they be normalized?
• Birthdate is better to store then age. You don't want to schedule someone for an interview on their birthdate (or if you're cruel by nature, you do want that :) ). Age can be derived from it and will also be correct at all times.

EDIT: Given that result is PASS or FAIL, simply declare the field a boolean and name it 'passed'. A lot faster.

---

One area where I could see a potential problem is the Interviewer being integrated in interview. Also I would like to point at the source channel in applicant, which could potentially get blobbed (depending of what you're going to store in there).

---

You don't seem to be violating any normalization rules upon first glance. It's not clear from your schema design, however, that the applicant_id is a referencing the applicant table. Make sure you declare it as a foreign key that references the applicant table when actually implementing the scehma.

Not to make any assumptions on your data, but can the result of a screening be stored in 4 characters?

---

Age and gender are generally illegal questions to ask in interviews so you may not want to record such things. You might want a separate interviewer table. You also might want a separate table that stores qualifications so you can search for people you have interviewed with C# knowledge when the next opening comes up. I'd probably do something like a Qualifications table that is the lookup for quals you want to add to the applicant qualfications table. Then you'd need the qualification id, applicantId, years, skill level in the Applicant Qualification table.

I notice results is a varchar 4 field, I assume you are planning to put Pass/fail in it. I would consider having a numeric score as well. The guy who got 80% of the questions right passed but the guy who got 100% of them right might be the better candidate. In fact for interviews I might have interview questions and results tables. Then you can record the score and any comments about each question which can help later in evaluation of a lot of candidates. We did this manually in paper spreadsheets once when we were interviewing several hundred people (we had over a hundred openings at the time and this was way before personal computers) and found it most helpful to be able to compare answers to questions. It's hard to remember 200 people you interviewed and who said what. It might help later when you have a new opening to find the people who were strong onthe questions most pertinent to the new job who might not have been given a job at the time of the interview(5 excellent candidates, 1 job for instance).

I might also consider a field to mark if the candidate is unaccepatble for ever hiring for some reason. Such as he committed a felony or he lied on the resume and you caught him or he was just totally clueless in the interview. This can make it easy to prevent this person from being considered repeatedly.

---

I think that your DB structure has a lot of limitation for future usage. For example you can even have a description of the exam because this stable store the score and exam date. It may by that this kind of information are already stored in another system and you have to design only the result container. But even then the exam, screen and interview are just a form of test, that why the information about should be stored in one table and distinguished by some type id. If you decide to this approach you have to create another table to store the information about result

So the definition of that should look more like this:

TEST 
 TEST_ID
 TEST_TYPE_ID ref TEST_TYPE - Table that define the test type 
 TEST_REQUIRED_SCORE - The value of the score that need to be reach to pass the exam. 
 ... - Many others properties of TEST like duration, expire date, active inactive etc. 

APPLICANT_RESULTS 
 APPLICANT_ID ref APPLICANT
 TEST_ID = ref TEST
 TESTS_DATE - The day of exam 
 TEST_START - The time when the test has started 
 TEST_FINISH - The time when the test has ended 
 APPLICANT_RESULT - The applicant result of taken test. 

This kind of structure is more flexible and give the easy way to specify the requirements between the test in table like this

TEST_REQUIREMENTS - Table that specify the test hierarchy and limitation   
 TEST_ID ref TEST
 REQUIRED_TEST ref TEST
 ORDER - the order of exams 

Another scenario is that in the future your employer will want to switch to an e-exam system. In that case only think what you will need are:

• Create table that will store the question definition (one question can be used in exam, screen or interview)
• Crate table that will store the question answers.
• Create table that will store the information about the test question.
• Create table for storing the answer for each question given from applicant.
• A trigger that will update the over all score of test.