Home Forums SQL Server 2008 SQL Server Newbies I wish to compose Normalised tables for the below requirment Please Help. RE: I wish to compose Normalised tables for the below requirment Please Help.

  • March 17, 2013 at 4:40 pm

    #1597929

    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)