Results 1 to 4 of 4

Thread: Normalization

  1. #1
    Join Date
    Dec 2004
    Location
    greenock, scotland
    Posts
    4

    Question Normalization

    Hey

    Was looking for a wee bit of help with normalization. Was just wondering if i've done this one right. Any help much appreciated, thanx

    unf

    StaffNumber (pk)
    StaffForename
    StaffSurname
    StaffAddress
    StaffTown
    StaffCity
    StaffTelNo
    StaffSex
    StaffDOB
    StaffNIN
    JobPosition
    AllocatedWard
    Salary
    SalaryScale
    Hours/Week
    PaidW/M
    ContractP/T
    QualificationType
    QualificationDate
    QualificationInstitution
    WorkExPosition
    WorkExStartDate
    WorkExFinishDate
    WorkExOrganisation

    1nf

    StaffNumber(pk)
    StaffForename
    StaffSurname
    StaffAddress
    StaffTown
    StaffCity
    StaffTelNo
    StaffSex
    StaffDOB
    StaffNIN
    JobPosition
    AllocatedWard
    Salary
    SalaryScale
    Hours/Week
    PaidW/M
    ContractP/T

    StaffNumber(pk)(fk)
    QualificationID (pk)
    QualificationType
    QualificationDate
    QualificationInstitution

    StaffNumber (pk)(fk)
    WorkExID(pk)
    WorkExPosition
    WorkExStartDate
    WorkExFinishDate
    WorkExOrganisation

    2nf


    StaffNumber(pk)
    StaffForename
    StaffSurname
    StaffAddress
    StaffTown
    StaffCity
    StaffTelNo
    StaffSex
    StaffDOB
    StaffNIN
    JobPosition
    AllocatedWard
    Salary
    SalaryScale
    Hours/Week
    PaidW/M
    ContractP/T

    StaffNumber (pk)(fk)
    QualificationID (pk)(fk)

    QualificationID(pk)
    QualificationType
    QualificationDate
    QualificationInstitution

    StaffNumber(pk)(fk)
    WorkExID(pk)(fk)

    WorkExID(pk)
    WorkExPosition
    WorkExStartDate
    WorkExFinishDate
    WorkExOrganisation

    3nf

    StaffNumber(pk)
    StaffForename
    StaffSurname
    StaffAddress
    StaffTown
    StaffCity
    StaffTelNo
    StaffSex
    StaffDOB
    StaffNIN
    JobPosition
    AllocatedWard
    SalaryScale(fk)
    Hours/Week
    PaidW/M
    ContractP/T

    SalaryScale(pk)
    Salary

    StaffNumber (pk)(fk)
    QualificationID (pk)(fk)

    QualificationID(pk)
    QualificationType
    QualificationDate
    QualificationInstitution

    StaffNumber(pk)(fk)
    WorkExID(pk)(fk)

    WorkExID(pk)
    WorkExPosition
    WorkExStartDate
    WorkExFinishDate
    WorkExOrganisation

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i think i understand why you have split off separate tables here --

    StaffNumber (pk)(fk)
    QualificationID (pk)(fk)

    QualificationID(pk)
    QualificationType
    QualificationDate
    QualificationInstitution

    StaffNumber(pk)(fk)
    WorkExID(pk)(fk)

    WorkExID(pk)
    WorkExPosition
    WorkExStartDate
    WorkExFinishDate
    WorkExOrganisation

    it seems you are modelling a couple of many-to-many relationships, and having a "junction" or relationship table with a composite primary key consisting of two foreign keys is absolutely correct

    however, i don't understand why you have start/end dates where you do

    this would imply the same start/end date for every staff member who had that qualification or work experience

    those dates (and similar fields) should be in the junction table, as attributes of a specific staff member's qualification or work experience
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Posts
    54

    Without the rigor

    Hi,
    A casual glance.... riddle me this.. was is a 'staff person born with' - the same thing every person is born with.

    You're not born with a name (your parents gave that to you and it may change many times and in with different contexts)
    You're not born with an address
    You're not born as a 'Staff' or any work attributes, you don't get those until you're older

    So that gets you to thinking about what attributes really define a person, an employee, and an address (or contact point), and a qualificiation

    You'll find there's not much if anything in person.

    Vmusic
    ~Keep the Apples in the Apple Bucket, and the Oranges in the Oragne Juice~

  4. #4
    Join Date
    Dec 2003
    Location
    Canada
    Posts
    710
    The real challenge here is there are no business rules or sample data to work with. There are many ways to group these fields. Most of them could be wrong.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •