8

I am a newbie on creating database... I need to create it for my recruitment web application.

My application needs to schedule screenings, exams and interviews of applicants and save result in the database..

My database schema is as follows:

enter image description here

My problem is 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?

Improve this question
1
  • 1
    I don't think anyone without specific knowledge of the business domain/enterprise can just look at your data model and give you realistic advice about keys, constraints, data types, etc. I can tell you, though, that any nullable columns will violate a strict interpretation of 1NF.
    – onedaywhen
    Jan 17, 2012 at 8:26

2 Answers 2

Reset to default
10

There are few things to consider other than normalization. For instance, you have a column for AGE. Are you going to update that every year? How will you know when to do that? The same goes for years of experience.

There are some columns that will probably have multiple values for each applicant: School, Course, etc.

You may also want to check your optionality on those relationships. Right now an applicant must have a related exam, but an exam does not have to have an applicant associated with it. I'm guessing that's backwards to how things work in real life. You have similar issues with all the other relationships.

It helps if you read out the relationships after you create them.

Improve this answer
1
  • 1
    There are few things to consider other than tables and columns :) My country has data protection laws (time limit on data retention, person has right of access to their data, etc) and age discrimination laws that should make employers' at least think twice before recording someone's age at the interview stage.
    – onedaywhen
    Jan 17, 2012 at 8:20
5

I guess that you actually need a many to many relationship between your Applicant table and the other main tables (Screening and Exam mainly). That if you'd have a screening contain more than one applicant (and the similar situation for the other tables).

I'd see an interview with a single candidate, but an exam or screening session with more than one candidate. In this case you will need a relation table that will link Screening with Applicant.

See here about Many-To-Many relationships.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.