1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

First Large Database Project - Seeking Advice

Discussion in 'Databases' started by Matthew C, Mar 16, 2019.

  1. #1
    To give you the short of it, I am working on a new personal project for when I go back into teaching or programming. I am working on a website that will have two types of users - Students and Teachers.
    SEMrush
    Teachers should be able to -
    1. Make tests
    2. View/ edit tests
    3. Create students and classes
    4. Administer tests to classes of students
    5. View tests after completion to amend automatic marking.
    6. View mark sheets of data from tests taken over time.
    Students should be able to -
    1. Take tests that have been assigned
    2. Revise tests they have completed
    3. View all of their grades over time
    I have attached my design of the database, however it has gotten a little complex and I am unsure if I have approached the layout correctly, any and all advice on how to properly structure this database (or any redundancies I have created) would be greatly appreciated as I do not often work with databases.
    DatabaseRelationTableVer2.jpg
     
    Solved! View solution.
    Matthew C, Mar 16, 2019 IP
    SEMrush
  2. #2
    Looks correct to me. Nothing is denormalized. I believe the Class reference should stay in the Test table as well. That way you don't need a unique entry in the Tests table for each class taking the test, which is the other way to accomplish it.

    Also, my personal preference is to have joining tables have their own primary key and use a unique composite index on the referenced columns to prevent duplicates.

    IE for the "Teachers of subject" table:
    TeacherSubjectId
    SubjectId
    TeacherId

    Same for the Teachers of Class and Students of Class tables.
     
    jestep, Mar 20, 2019 IP
  3. Matthew C

    Matthew C Greenhorn

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    11
    #3
    That makes a lot of sense, will update my design with those changes. Thank you for the advice, I really rarely get to work with databases.

    Glad to know about the primary key on many to many relationships, I was unsure about best practice on this.

    I will update this design now and start working on splitting functions between front end and back end.
     
    Matthew C, Mar 20, 2019 IP