Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2012
    Posts
    4

    Unanswered: Database initial design

    Hello all dbforumers,

    I have received much support continuously by reading dbforums.com and have always found many solutions. Currently, I am in need for some 'logical' help in how to initially set up the tables design.

    Situation:
    I have already completed a database for a local language center that is aiming to manage it's Student, Teacher and Course data (Langauge Course, Level, Course Payments, Book Payments, etc).
    I have tblCourses, tblTeachers(teacher data), tblStudents(student data), tblCoursePayments & tblBookPayment(monthly payment data for courses and books).

    Problem:
    Teachers can teach multiple language courses & levels as well as Students can be attending -more than one - language courses simultaneously. This is a problem to me, as the way I have set up the database currently, the only solution I see is to add the same student as a new record with a different ID number, which is causing me difficulties in organizing student payment.

    I have tried different indexing for tblTeachers, as well as tblStudents, but I am blocked.

    Can someone please give me any advice as to how to set up the initial design in a smart way?

    Best regards,
    M.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    look up association tables, or as I used to call them cross reference tables
    https://www.google.co.uk/#hl=en&outp...w=1366&bih=681
    ferinstance having defined your entities for student and courses, defien an entity which records what courses a student is attending. this will be the primary key of the student and course tables as the primary key, and any other relevant infroamtion to just that intersection. you could then use the primary key of that intersection table (the student + course ID's) in your course payments table so you know what courses a student has paid for (and presumably not paid for)
    essentailly its the same with teachers and courses, have an intersection table which identifies what teachers are capable of teaching what courses.. however in order to know what teacher is teaching a specific instance of a course then you need to define a coursesheld table (which identifies the course + dates)
    if you need to record which teachers taught specific days on a course then you need to push this down another level.

    Bear in mind there would be one row for each intersection. if only one teacher taught a specific instance of a course then there would be one entry in the intersection table for that course and that teacher. two teachers woudl have two rows (one for each teacher for that course)
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2012
    Posts
    4
    oh wow, thank you healdem.
    I guess that is indeed the logic I was looking for.
    Association/join tables. Alrighttt.

    I know have the tblTeachers and tblStudents held together by a new table called tblCourseNames with teacherID and studentID as one-to-many.
    Now, I guess I need to get on with queries.

    thanks again,
    M.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id expect primary tables to be
    teachers
    students
    courses
    then have association tables identifying what
    courses a student is enrolled in
    courses that a teacher is qualfiied to teach in

    a course is an atomic entity in its own right
    its up to you if when definign what course a student is enrolled in is directly linked to the course details or a sub table off coursedetails (eg a specific course instance (say English running from Sept 2012 to May 2013)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Aug 2012
    Posts
    4
    healdem,

    You certainly introduced me with a whole new way of organizing tables, thank you so much for that. I am really working on a new level now.

    Now, what if I wanted to add Levels (Starter, Intermediate, Advanced), to each course?
    Students actually attend a certain level of a certain course (i.e English - Advanced; German - Intermediate).

    Can you please point me in the right direction here, healdem?

    best of the best regards,
    M.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is the attribute of language level a function of the student or the course
    Can multiple levels be catered for in a single instance of a course ie mixed levels in the same course.
    Is the same course offered as different levels. Eg English course runs Sep to Dec a intermediate. JAN TO MAR ADVANCED. You need to understand what you are trying to model.

    Off hand id expect the level if the course to be determined in the course description and refered to in the course instance and derivable from student enrollment.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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