Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Exclamation Unanswered: table relationship problem

    i have 6 tables
    -tblStudents_info( includes students names,address..etc)
    -tblStudent_Classes(includes students classes and streams)
    -tblStudent_Subjects(includes students subjets based on a certain combination.)
    -tblStudent_Marks(includes student marks in various subjects)
    -tblExam_Name(includes examID,Exam_Name)
    -tblExam_Period(inclues PeriodID,Period_Name)

    problem comes in how do i relate the tables (tblStudent_Marks,tblExam_Name and tblExam_Period)in the case where there are 3 Exams done in each period(3 periods)

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    i think your design is close, but not quite there.
    student assessments can be a minefield

    students, courses and subjects are all good candidateas for top level tables

    however you need to know which students are enrolled on which courses (StudentsCourses)
    you need to know which subjects are available on which courses (CourseSubjects)
    from that you need to know which subjects a student is doing. (Students\courseSubjects) if the institution enforces no choice in subjects for a course year then you don't need a separate table for subjects. effectivley that gives you the student registration details.

    it gets trickier when you try to model a specific year/stage. its gets very tricky if a subject is available in a specific year(s)

    you need to know what type assessment the institution uses (AssessmentTypes) be that exam, essay, practical, demonstration / whatever
    from that you can define the assessments for that subject (SubjectAssessments)

    having doen that you can then define a students assessment. persoanlly Ive also added the mark history (which allows for all manner of adjustments to be made (eg +w actual mark, -x penalty for beign late, -y for plagarism, Z for end of year review and so on.

    is that 'the' right approach, I doubt it, it depends on what your homework requires and what you can explain
    Attached Files Attached Files
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    3
    thank you healdem for your rep, i think i will post the whole design, the part with the head-ake is "Relationship partB"
    the design works well in my case, However the "Relationship partB" has issues, i tend to think i may require "many-to-many" relationships but really dont know exactly where to begin.
    i have attached the screenshots.meanwhile, i will look into your design concept. thankyou.
    Attached Thumbnails Attached Thumbnails Relationship partA.jpg   relationsh partB.JPG  
    Last edited by matiyas; 09-09-13 at 09:41.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    effectively StudentsCourses in the above PDF model implements a many to many relationship, its more commonly known as an intersection table.
    it allows many courses to be associated with many subjects, but there can only ever be one pairing of course and subject

    whenever you see a table with lots of columns with similar names, suffixed by numbers its nearly always a symptom of flaky design

    In my opinion you need to go back to basics and make certain your tables are normalised before proceeeding much further
    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
  •