#1
  1. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375

    Creating classes/lessons database?


    so I am creating a database for a school system.

    Currently I want to create tables like this:

    students -> student_class -> class -> class_schedule

    is this the right approach? (Each word above is a table)

    the other consideration I have is how do I code the database so that next year, the students will move to the next year group?
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    Originally Posted by paulh1983
    is this the right approach?
    more or less, yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375
    cheers. would you do it a different way?

    Also each class_schedule will have 1..* activities. so:

    class_schedule -> schedule_activities -> activities.

    Also: the other consideration I have is how do I code the database so that next year, the students will move to the next year group?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    Originally Posted by paulh1983
    cheers. would you do it a different way?
    i dunno, i can't see your assignment/specs from here


    Originally Posted by paulh1983
    Also: the other consideration I have is how do I code the database so that next year, the students will move to the next year group?
    your application will ahve to issue an UPDATE statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,971
    Rep Power
    375
    Thank you again for helping me. I am still a bit unclear on one thing:

    students -> student_class -> class -> class_timings

    class (class_id, teacher_id, class_desc)
    class_timing (class_id, day, time)


    Now how do I "schedule" each class in the database so that i have something for example:

    Class 1 - Monday 9th October
    Class 1 - Monday 16th October
    ...

    I was thinking to simply have another table:

    schedule (class_id, day, time)

    then the application can make sure that for example: if a class only happens on Monday, the teacher/student cannot schedule that class for a Tuesday?


    Another few questions:

    1. Do i have one table for student and another for teacher? or should I have one table and have an extra column called "type"

    2. Do i store the timings as day/times or day/periods (as is common with schools they have periods/lessons)

    http://www.databaseanswers.org/data_models/student_rosters/index.htm seems to have two tables and using periods so I wondered.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,126
    Rep Power
    4274
    these questions you have will all be easier for you to answer once you start loading the tables with realistic sample data

    Comments on this post

    • paulh1983 agrees : Thanks for taking the time to help me
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo