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

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

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~

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.