Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2007
    Posts
    3

    Training DB design question...

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hi todd!

    what is your question, please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2007
    Posts
    3
    Sorry -
    my question is "how should I best structure the table 'events' to achieve my goals, or should I consider additional tables?".
    ToddF

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    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

  7. #7
    Join Date
    Sep 2007
    Posts
    3
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •