[ToddF]

Hey all,
I'm a noob at DB design - so any direction you can offer is much appreciated.

What I'm trying to achieve is a db which allows me to record & query feedback from students attending classes on various subjects, presented by various people employed by various companies. Before any instructor can teach a course, he/she must be certified on that subject. Each instructor can be certified for one or several subjects.

So far, what i'm thinking is:
tbl_employers
- employerID
- name
- street
- state
- country
- Pointofcontactname
- Pointofcontactphone
- Pointofcontactemail

tbl_instructors
- instructorID
- employerID
- name
- cert1subject
- cert1date
- cert2subject
- cert2date
- cert3subject
- cert3date
- cert4subject
- cert4date

tbl_courses
- courseID
- subject
- lengthdays

tbl_events (and this is where I'm having trouble...)
I want each 'event' to represent one instructor delivering one course on one date in one location, so I'm imagining:
- eventID
- courseID
- instructorID
- date
- location (I believe the last 4 things will make this instance unique)
Now, I'm hoping to collect several fields of feedback from each student for each event (satisfaction-survey stuff). My quandry is how to organize this...
Each student may attend more than one event over time, but I don't expect to need to query-by-student. I DO want to be able to find:
1) who-taught-what-when
2) what-feedback-came-from-this-event
3) what-feedback-came-from-all-this-instructors-events
4) what-feedback-came-from-all-this-courses-events
5) what-values-from-feedback-field-x-over-all-events
6) what-values-from-feedback-field-y-over-all-events
7) if it's easy, I might want to query what-values-from-student-x-over-all-events but this is not a priority.

For experienced DB folks this is probably simple, but my little brain is spinning.
If anybody can suggest a way forward, I'll be very interested to hear!

Thanks in advance.

ToddF

[r937]

hi todd!

what is your question, please?

[ToddF]

Sorry -
my question is "how should I best structure the table 'events' to achieve my goals, or should I consider additional tables?".
ToddF

[r937]

well, since you want to collect feedback from more than one student per event, i would suggest that you need another table for feedbacks, so that each event can have multiple feedbacks

[mike_bike_kite]

Todd

• Shouldn't the instructor certificates be in a separate table - at the moment you're limited to 4. Generally if you're naming a field field1 field2 etc then this implies this field should be in a separate table.
  
• Shouldn't course have a description field. Would you ever need to know what courses need to have been completed before doing another,higher level, course?
  
• I can't work out what the eventID field is for - wouldn't it be better just using the real fields (courseID, instructorID, date & location) rather than creating an artificial one.
  
• The date field here should be called something else otherwise it will most likely conflict with a key word somewhere down the line.
  
• r937 is definitely right that you need another table for feedback (probably containing the fields above to show what course they were doing and then studentID, score, comment).
  
• You may want to have a Student table as well - it might be useful for sending out invitations to new courses etc.
  
• I'd also get rid of those tbl_ prefixs on each table name.

Mike

[Pat Phelan]

This smells like a RFHW to me. Can you post a URL link or a PDF image of your assignment as it was passed out in class so that we only have to solve the problem once?

-PatP

[ToddF]

Hey all,
Thanks for the feedback.
Actually, I don't know what "RFHW" is - I infer it's some education thing.
but in fact I am employed by a company, and I am a noob at databases, and I am trying to get a project done which requires a database, and I have no budget and no help (except you folks!) Sorry if it's elementary for y'all. I do appreciate your time and expertise.

Mike-bike-kite - I understand what you're saying. All good ideas, and r937 too - thanks for the feedback table idea. Now that you mention it, it seems natural.

I'll work it as you folks recommend, and thanks again.
Todd