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_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:
- 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:
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!
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.
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.