Results 1 to 4 of 4
  1. #1
    tgould is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    2

    Database design suggestions for Student and Classes

    Hello,

    I am looking for suggestions on design of a database. I have 1000's students and multiple classes that run each year. The Students will only complete some of the classes. My plan is to create a Table with the class names and date of class (ClassT). I have a Table with all the student info (StudentT).



    Should I create a Table with a column with names of the classes (ClassT) as a lookup and a column with the students (StudentT) as a lookup.
    Then run a query to show class rosters.

    Thoughts or suggestions would be helpful.

    Thanks

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Should I create a Table with a column with names of the classes (ClassT) as a lookup and a column with the students (StudentT) as a lookup.
    Then run a query to show class rosters.
    that is the usual way to do it - though I would avoid using lookups in a table. Use them on a form instead.

    My plan is to create a Table with the class names and date of class
    this should really be two tables, one for classes and one for class dates

    tblClasses
    ClassPK autonumber
    ClassName text
    ...
    ...

    tblClassDates
    ClassFK long - link to tblClasses
    ClassDate date/time

  3. #3
    tgould is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2017
    Posts
    2
    Ajax- Thank you for reply.
    Would you create a separate table for every class roster? Or use a query to create the roster?

    Below is "FK" meaning Primary Key?

    [/QUOTE]tblClassDates
    ClassFK long - link to tblClasses
    ClassDate date/time[/QUOTE]

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    No PK=Primary Key (the one side of a one to many relationship). FK=Foreign (or Family) Key, the many side of a relationship - one class can have many dates

    As surmised by you for students/classes - one student can have many classes - AND one class can have many students. This is a many to many relationship which is handles through a separate table, which you have suggested doing

    So one class can have many dates, but also one date can have many classes. However there is little point in having a separate table for dates per the student/class example. But really depends on what you are trying to achieve.

    I would definitely not have a separate table for each roster, use a query to find students per class or classes per student

Please reply to this thread with any new information or opinions.

Similar Threads

  1. ERD of student course database design
    By bazzano in forum Database Design
    Replies: 4
    Last Post: 03-11-2015, 07:31 AM
  2. Replies: 8
    Last Post: 07-24-2014, 11:11 PM
  3. Student Results Database Design
    By wally2k14 in forum Database Design
    Replies: 4
    Last Post: 02-06-2014, 03:00 PM
  4. Examiner Database Design Suggestions
    By seth1685 in forum Database Design
    Replies: 1
    Last Post: 01-05-2012, 10:11 AM
  5. Suggestions for Form Design
    By KrisDdb in forum Forms
    Replies: 2
    Last Post: 12-08-2011, 02:31 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums