If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Normalization

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-22-05, 09:53
gillyb gillyb is offline
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old 01-22-05, 10:21
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 01-22-05, 14:46
Vmusic Vmusic is offline
Registered User
 
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~
Reply With Quote
  #4 (permalink)  
Old 01-26-05, 02:17
certus certus is offline
Registered User
 
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.
__________________
visit: relationary
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On