I wish to compose Normalised tables for the below requirment Please Help.

Question

I wish to compose Normalised tables for the below requirment Please Help.

kiran.vaichalkar

Ten Centuries

Points: 1056
More actions
March 17, 2013 at 1:51 pm

#275298

There is requirement as below to create the Data Model for estimation in No of tables as of now
Openings
Resumes Available Per Opening
Candidates Contacted
Skills Required(Programing Langs Only: JAVA/.NET/C C++/Perl etc)
OS Knowledge (Eq. Windows/Linux/UNIX etc)
Candidates Status.(Reject/Select/Hold)
Interview details
Assumption: CandidateID created only when ResumeID available for a candidate once contacted.
I Just gave the crack which is below. Please correct to the most granulized form,
1) Opening( OpenID, OpenName)
2) Resumes( ResumeID, CandidateID, LastDateOfUpdate,Resume)
3) ResumeAvailability(OpeningID, ResumeID) - (Derived Table)
4) Skills(SkillID, SkillName,SkillDuration)
5) SkillAvailability(SkillId, CandidateID) - (Derived Table)
6) OS(OperSID, OperSName, OperSDuration)
7) OSAvailability(OperSID, CandidateID) - (Derived Table)
8) InterviewDetails(InterviewID, EmployeeID, CandidateID, DOI, Rating,Status_F) ; Date of Interview - DOI ; STATUS_F(Entries Allowed S,R,H only)
Can you please guide me so to make this more appropriate to real life.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

I'd split the candidate out into a separate table and I'd have an interview header and detail table so that when you have more than one EmployeeID at the interview, you're not denormailizing the data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiran.vaichalkar Ten Centuries Points: 1056 More actions · Answer 2

Thanks a lot Jeff. I would like to add 1 more table to that list.

1) Opening( OpenID, OpenName)

2) Resumes( ResumeID, CandidateID, LastDateOfUpdate,Resume)

3) ResumeAvailability(OpeningID, ResumeID) - (Derived Table)

4) Skills(SkillID, SkillName,SkillDuration)

5) SkillAvailability(SkillId, CandidateID) - (Derived Table)

6) OS(OperSID, OperSName, OperSDuration)

7) OSAvailability(OperSID, CandidateID) - (Derived Table)

8) InterviewDetails(InterviewID, CandidateID, DOI, Rating,Status_F) ; Date of Interview - DOI ; STATUS_F(Entries Allowed S,R,H only)

9) Interviewers( InterviewID, EmployeeID);

10) Candidate(CandidateID, FirstName, LastName, ResumeID, DOB, Gender, LastDateOfUpdate).

Now will this be the best of the Normalised set of table formed for the discussed requirement?

Please Help me understanding this.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

kiran.vaichalkar (3/17/2013)
Thanks a lot Jeff. I would like to add 1 more table to that list.
1) Opening( OpenID, OpenName)
2) Resumes( ResumeID, CandidateID, LastDateOfUpdate,Resume)
3) ResumeAvailability(OpeningID, ResumeID) - (Derived Table)
4) Skills(SkillID, SkillName,SkillDuration)
5) SkillAvailability(SkillId, CandidateID) - (Derived Table)
6) OS(OperSID, OperSName, OperSDuration)
7) OSAvailability(OperSID, CandidateID) - (Derived Table)
8) InterviewDetails(InterviewID, CandidateID, DOI, Rating,Status_F) ; Date of Interview - DOI ; STATUS_F(Entries Allowed S,R,H only)
9) Interviewers( InterviewID, EmployeeID);
10) Candidate(CandidateID, FirstName, LastName, ResumeID, DOB, Gender, LastDateOfUpdate).
Now will this be the best of the Normalised set of table formed for the discussed requirement?
Please Help me understanding this.

It's closer but I don't believe so. For example, there's no need for the ResumeID in the Candidate table.

I also don't understand the emphasis on OS nor what OperSDuration is nor why you change your naming convention from OS to OperS here and there. If someone can't understand it without you having to explain it, then it's probably not ready for primetime.

I also believe that what you have as "InterviewDetails" should just be an InterviewHeader and that DOB and Gender should NOT be included in that table. That needs to be in the Candidate table.

You also have no way of tracking multiple interviews of the same candidate. That's what a new InterviewDetails table would do.

As an additional suggestion, there's no way in hell I'd store the resume in the database because it will require a blob datatype because, unless you have 2012 or above, you can't rebuild clustered indexes in an online fashion if the table has any blob or XML columns in it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kiran.vaichalkar Ten Centuries Points: 1056 More actions · Answer 4

Agaiin Thanks Jeff!!!!

Can we consider the below way?

*ResumeID is used for tracking changes in resume(Date, new file etc)

*Naming Conventions for OS are now matched.

6) OperS(OperSID, OperSName, OperSDuration)

*DOB and GENDER are in Candidtate Table now.

Also

Can this table No 8 InterviewDetails, help us getting multiple interviews of a candidate if we use 'Group by ON CandidateID'?

Is there any other concept of storing database to our local disks and link it to our DB Column entries?