Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

I wish to compose Normalised tables for the below requirment Please Help. Expand / Collapse
Author
Message
Posted Sunday, March 17, 2013 1:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:37 AM
Points: 57, Visits: 115
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.
Post #1432021
Posted Sunday, March 17, 2013 3:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 35,549, Visits: 32,145
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432033
Posted Sunday, March 17, 2013 3:51 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:37 AM
Points: 57, Visits: 115
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.
Post #1432034
Posted Sunday, March 17, 2013 4:40 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:00 PM
Points: 35,549, Visits: 32,145
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432044
Posted Tuesday, March 19, 2013 1:29 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 7, 2014 12:37 AM
Points: 57, Visits: 115
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?
Post #1432506
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse