Relationships again!

[tmarsh]

Hello. I posted a while ago on a database I'm working on. Still having trouble with relations. These are the fields in 1nf:

FNAME
LNAME
JOB_TITLE
BASE
DEPT_WARD
COURSE
DATE(S)_ATTENDED_COURSE
COURSE_TRAINER(S)
COURSE_VENUE
CERTIFICATE_ISSUED

The database keeps a record of various training courses for health service staff. From answers to previous posts I figure I need:

TBSTAFF
STAFF_ID
FNAME
LNAME
JOB_TITLE

TBCOURSE
COURSE_ID
COURSE

TBTRAINING_DONE
STAFF_ID
COURSE_ID
DATE_TRAINED
TRAINED_BY
CERT_ISSUED

I think that's ok but it's when I try to link them I get into trouble. Assuming the above is correct do I have:
TBSTAFF linked by STAFF_ID to TBCOURSE-COURSE_ID
TBSTAFF linked by STAFF_ID to TBTRAINING_DONE-COURSE_ID

I'm not sure how base and dep/ward work either. I need to know a person's base and dept e.g. hospital x, ward y.

Thanks.

 

[ColinEssex]

The Courses table is a Lookup table so is linked to the TrainingDone table.

So you have

TBStaff.[StaffID] to TBCoursesDone.[StaffID] then have

TBCoursesDone.[CourseID] to TBCourse.[CourseID]

The hospital site and ward can come from another LookUp table for TBWards with the same fields as your Patient Administration System - in fact download the Ward masterfile from PAS and its done for you. Then you'll have

WardCode
WardName
HospSite


Use that in a ComboBox on the users demographic screen.

Col

 

[Mile-O]

These are the fields in 1nf:

DATE(S)_ATTENDED_COURSE
COURSE_TRAINER(S)

The fact that these fields are plurals would indicate that they are not atomic and can not be considered first normal form.

 

[tmarsh]

Still having problems. Would someone take a look at the attached database and tell me where I'm going wrong? I'm not sure how to link a staff member to base/dept.

Thanks.