SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
kiran.vaichalkar
kiran.vaichalkar
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 132
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206691 Visits: 41960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kiran.vaichalkar
kiran.vaichalkar
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 132
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
Jeff Moden
SSC Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206691 Visits: 41960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
kiran.vaichalkar
kiran.vaichalkar
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 132
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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search