Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2010
    Posts
    5

    Unanswered: How to Design Relationships for School Enrollment/Grades Database

    Hi. I’m trying to make a database in Access 2007. I need help with the general design and relationships.

    This DB will work as grade/enrollment management for my high school. The main idea is to make it easier for teachers to input grades and calculate rankings as they’re currently doing it all recordkeeping with pen and paper. I’d also like to make it easier for the administrators to keep track of enrollment data (courses, grades, graduates, drop outs, contact information) from year to year. I was hoping to find a template online but, surprisingly and disappointingly, couldn’t. I have never used any database software (including Access) before trying to create this database so please excuse any rookie mistakes.

    I started with the tables below. Asterisks indicate the primary keys.

    Problems/limitations/notes:
    • Students take the same courses all year.
    • I’m unsure how to link the grades to each individual course.
    • I’m also unsure of how to relate the tables to each other or whether I should merge some of them (for example, have fields named like Term1Course1Achievement in the Enrollment Table).
    • I want to keep track of teacher enrollment, too. This way, I can create a form for teachers to use to enter grades (and for the administration to print report cards).

    I hope this is easy to understand and includes all the information you need. Please let me know if you have any ideas or need more information. Thank you so much for your help! The staff at my school really appreciates it too!

    Jennifer


    Teachers

    ID*
    Teacher ID
    Last Name
    First Name
    Title –> data source: Principal, Deputy Principal, Senior Lecturer, TA
    Degree –> data source: BA, MA, DipEd, Other
    Department –> links to Department (multiple okay)
    >>>>>>Teachers Query
    FileAs (Last, First Name)
    Teacher (First Last Name)

    Students

    ID*
    Student ID
    Level
    Last Name
    First Name
    Guardian -> Links to Guardians (multiple selections okay)
    >>>>>>Students Query
    FileAs (Last, First Name)
    Student (First Last Name)

    Guardians

    ID*
    Last Name
    First Name
    >>>>>>Guardians Query
    FileAs (Last, First Name)
    Guardian (First Last Name)
    Enrollment
    ID*
    Student –> link to Students
    Year –> Datasource: 2010,2011,2012…
    Homeroom –> link to Homerooms
    Courses –> link to Courses (multiple selections okay)
    Grades Term 1 –> link to Grades
    Grades Term 2 –> link to Grades
    Grades Term 3 –> link to Grades
    Grades Term 4 –> link to Grades

    Grades
    ID*
    Course1Achievement –>input %
    Course 1Attitude –>datasource: a,b,c,d,f
    Course 1Conduct –>datasource: a,b,c,d,f
    Course 1Attendance –>datasource: a,b,c,d,f
    Course 1Effort –>datasource: a,b,c,d,f
    Course 1Attitude –>datasource: a,b,c,d,f
    Course 1Comments –>datasource: editable comments list (multi-selections okay)
    repeat above fields for Courses 2-7 and for Homeroom.
    >>>>>>Grades Query
    Term GPA
    Term Rank (based on Term GPA, compared to all level)
    Year GPA
    Year Rank (based on Year GPA, compared to all level)
    Course1Rank (based on marks for this term only, compared to all level)
    Repeat for Courses 2-7.
    Courses
    ID*
    Course ID
    Department –>datasource: list of departments
    Level –> datasource:1,2,3,4,5,6
    Course

    Homerooms
    ID*
    Level –>datasource: 1,2,3,4,5,6
    Homeroom

  2. #2
    Join Date
    Oct 2010
    Posts
    51
    I'm not sure of the technical name for these types of tables. I've worked with databases for 4 years and have mostly learned on my own without any specialized training so take my suggestions as you will.

    My suggestion would be to have what I call a Linking table (Whether that's the right name or not, I don't know) to join the two tables that may have a many to many relationship.

    No need to store the Guardian in one field if multiple Guardians are possible.
    Create a table StudentGuardian that stores the StudentID and the GuardianID then you link the Student table on StudentID, and on the other side you link the Guardian table on GuardianID.

    Same for Enrollment table. You have an EnrolledCourses[EnrollmentID, CourseID] table with Enrollment linked on EnrollmentID and on the other side the Courses table linked on CourseID.

    Seems a bit complicated to set up, but it may save time in the long run where maintenance is concerned.

  3. #3
    Join Date
    Nov 2010
    Posts
    5
    Thanks, bdabaum! That makes perfect sense. My only question is how to relate the grades to the enrollment.

    For example, two students in the same level may have these classes:
    Student 1: Art, English3, Math2, Physics
    Student 2: Agriculture, Biology, English3, PE3

    They both have English3, but they show up in a different order. Student 1 has English as Course2. Student 2 has English as Course3. This would not be a problem except that I want to have teachers be able to select their classes and enter all enrolled students' grades. So if I teach English3, I should be able to open a form that lets me see all enrolled students (in the datasheet view, not the regular form view) and enter their grades. Perhaps this form could be created via a query? Something under Criteria like
    Course1=* OR Course2 =* OR Course3=* OR ...
    Where * = the current course selected by the teacher (e.g., English3).

    Any ideas?

  4. #4
    Join Date
    Oct 2010
    Posts
    51
    Well you would do a similar thing, but again with more flexibility you add complexity. This isn't too complex though.

    I might need some time to design an adequate structure that will last. I'm assuming your departments are similar to English, Math, Science, Arts, Social Studies, Health.

    I'm not sure about your high school, but are some classes available to multiple grade levels? IE Geometry, Pre-Calculus, Calculus, Physics, Algebra were all offered to various grades if you tested out of the pre-requisites. Is this the case for your school? Because that will add another bit to work on.
    You may find that it is necessary to break out a CourseLevel table [CourseID, AvailableLevel] so you may have two records in that table
    - CourseID1 is available for 9th graders and 10th graders.
    1, 9
    1, 10
    -Arts courses are generally more prone to this behavior.
    eg- CourseID -28 = Choir could be available for all grade levels.
    28, 9
    28, 10
    28, 11
    28, 12

    As far as getting the scheduling, I don't have a direct answer for that at the moment. But I will try to map it out and see how it might work best.

  5. #5
    Join Date
    Nov 2010
    Posts
    5
    Well, each course is only available at one level so that part is easy.

    I tried to set up the relationships as you suggested. I've attached a relationship report. I hope I did it the way you meant?

    Yes, departments are like English, Art, Science, etc. The school I'm at follows the NZ educational system, though, so Grades (as in levels) are called "Forms" and Homerooms are called "Form Classes." I've also used "Marks" to mean Grades (as in scores).

    As for relating marks to the appropriate courses, I was looking at the Northwind database and had an idea. What if I were to treat enrollment like an order? So each order (unique enrollment data group) would include products (classes), order details (marks), a customer (student), and a supplier (teacher). I could then make a form that's filtered by supplier (teacher). This form would only list orders (enrollment records) that contain the selected supplier's products (that teacher's courses). Then orders be used to enter details of each order's products (marks of teacher's courses). Maybe that was written in a confusing way. It would look like this:


    Supplier/Teacher: Smith, John

    Order/Enrollment#....Customer/Student....Product/Course....Details/Marks
    123.......................Galt,John............... .English2..............90%,A,A,B,B,Can do better.
    598.......................Trout, Kilgore...........English2..............98%,A,A,A, A,Great student.
    397.......................Karenina, Anna.........Literature...........97%,A,A,A,A,Keep it up.

    I'm not quite sure how I'd create this form using the tables I have but it seems like it would work. What do you think?
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2010
    Posts
    51
    For some reason this forum doesn't allow me to download things correctly. It might be a work thing, but I'll have to check this out at home. The data example looks pretty good from what I can tell.

  7. #7
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would think you would need some more tables. The Enrollment table seems to be serving more than one purpose. I would think that a table with the Year and the StudentID and possibly the Homeroom would be in one table. That would be your way of determining who should be in school for a given year.

    Then another table with StudentID, CourseID and Order would be used to define a students course load. You might call it tblStudentCourse (I like to keep related tables together with a naming convention). Then another would be for Student Grades. You might have the StudentID, CourseID, Term (Quarter), Grade. You could call it tblStudentGrade.

    You definitely want to stay away from tables having Course1 - Course7 in the table structure. If the school changes to 8 courses or 6 courses then you are either wasting space or you will have to update your database to get it to work for 8 courses.

    Also consider a student that doesn't have 7 courses. For that student you will have fields that are not being used. A properly designed database shouldn't have wasted space and should be flexible to grow to N courses.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    storing composite data in details / marks is asking for trouble
    far better to store such stuff separately.
    why?
    what happens if someone wants to know the number of grades by grade type, eg A,B
    or if someone wants to know howmany students scored 5A's, 4A' and so on.
    also its bad practice to mix data, and its bad rpactice to store numeric data as string / text / char
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2010
    Posts
    5
    First, thanks so much for your help. This already makes a lot more sense to me than it did two weeks ago.

    I did a bit of redesign based on your suggestions and looking at the Northwind DB.

    I still have marks listed by term. (A table with columns T1 Marks / T2 Marks / T3 Marks / T4 Marks). I can't foresee problems with that, but maybe I'm just not thinking hard enough.

    I did redo the courses to their own table. And each homeroom is now a course.

    I may not have followed some of your suggestions, healdem. I'm very new to Access and don't really understand a lot of the terminology yet. I didn't quite catch what you mean with this:
    storing composite data in details / marks is asking for trouble
    far better to store such stuff separately.
    I tried to look up the meaning of "composite data", but just found pages with more vocabulary words I couldn't understand. I assumed at first it was similar in meaning to concatenate -- a combination of more than one field. But I don't really know. I can't see what I'm storing like that.

    its bad practice to store numeric data as string / text / char
    I don't understand this bit, either. Sorry to be such a pain!

    I'm attaching the newest version of my db here. Ignore all the "Unused Forms" -- they're just from previous drafts. With the exception of making another table to deal with Marks (so I don't have "t1 marks/t2 marks..."), I think it is nearly ready to start creating forms. What do you think? Is it ready to go or are there still big problems?

    Thanks again!
    Attached Files Attached Files

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    composite data

    eg
    your 'Details/ marks"
    90% A,A,B,A
    should be on different rows in a sub table(s)

    a classic example of bad practise is say storing a date as a formatted text string representation rather than the inbuilt date/time datatype of that database
    another is storing numeric data as text eg 1,2,3,4.10,11,23...20
    sorts as 1,10,11,2,20,3,4... when stored as text NOT data
    if you mix percentages and grades then its harder to extract the data
    OK you have percentages as your first column, you can probabkly assume that most values will be 2 digits. its unlikely someone will score 100 and rarely below 10. but to extract that percentage you would have to construct a function or insert some VBA

    eg
    Code:
    public function ExtractPercent(mymangledcolumn as string) as integer
    ExtractPercent = null 'set up our default return value
    if isnull(mymangledcolumn) then exit function 'nowt supplied so return nowt
    chopat = instr(mymangledcolumn,"%") 'find where the percent sign is
    if chopat=0 then exit function 'lets get out of here.. no percentages found
    ExtractPercent = left(mymangledcolumn,chopat-1) 'remember instr returns where the symbol was found so we need to knock one off for the end of the percentage
    exit function
    ...now if you allowed the percentage to be anywhwere in that strin you have to search for the first numeric character
    ..the above code fails if say the percentage sign isn't there

    say you want to extract the grades, again a function is needed

    frankly its lazy design that limits the data's usefullness overtime.
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Nov 2010
    Posts
    5
    Healdem, thank you for your tips. I really want this database to help the teachers and students at my school and I appreciate all the help.

    Quote Originally Posted by healdem View Post
    composite data
    your 'Details/ marks"
    90% A,A,B,A
    should be on different rows in a sub table(s)
    I think you're referring to the "Marks" table, which shows the different types of marks earned (%, effort, etc.). As far as I can tell, Access stores everything in columns. I don't know how I'd do that in different rows. And I still don't really understand why it's bad to have percentages and letter grades in the same table. They are grades for different things. (% would be based on coursework, where as the letters are based on things like effort and attendance.) From an Access beginner's standpoint, it seems like the same thing as storing a phone number and a name as two different data types in the same table. They're different types of data, but they're reporting different things.

    At this point, I don't see why having these things in separate columns is a bad thing. The letter grades actually are stored as numbers. They link to the ID column from the "Marking Criteria" table, which looks like this:
    ID....Letter....EffortCriteria.....ConductCriteria
    1....A....Always tries hard.............Listens well.
    2....B....Usually tries hard.............Usually listens well.
    3....C....Average effort................Sometimes listens well.
    (etc)
    So an "A" actually is stored as the number "1". The percentages are stored as Single/Percent with 2 decimal places. I could change it to a long integer, but I don't really understand why I need to. Seems like I'm totally missing what you're trying to say.

    Quote Originally Posted by healdem View Post
    Code:
    public function ExtractPercent(mymangledcolumn as string) as integer
    ExtractPercent = null 'set up our default return value
    if isnull(mymangledcolumn) then exit function 'nowt supplied so return nowt
    chopat = instr(mymangledcolumn,"%") 'find where the percent sign is
    if chopat=0 then exit function 'lets get out of here.. no percentages found
    ExtractPercent = left(mymangledcolumn,chopat-1) 'remember instr returns where the symbol was found so we need to knock one off for the end of the percentage
    exit function
    ...now if you allowed the percentage to be anywhwere in that strin you have to search for the first numeric character
    ..the above code fails if say the percentage sign isn't there

    say you want to extract the grades, again a function is needed

    frankly its lazy design that limits the data's usefullness overtime.
    I don't understand this code at all. As I said, this is this is the first database I've ever made. I also don't understand why not knowing what you're doing makes you guilty of lazy design.

    I didn't see anywhere in the rules or FAQ that this board was for experts only, so I don't know why you're trying to make me feel like an idiot. I've never taken a class about design or studied computers. I took an online tutorial and am trying to now design this database as part of my volunteer work with a high school in a developing country. I've been upfront about the fact that I don't know what I'm doing; it's why I'm asking for help. I appreciate you trying to help but I don't really appreciate the sarcasm. Please try to remember what it's like to be a beginner. Thank you.

  12. #12
    Join Date
    Mar 2012
    Posts
    1
    how do i post a new post in the forum

  13. #13
    Join Date
    Sep 2015
    Posts
    1

    difference between marks and StudentMarks tables...

    Quote Originally Posted by jeni007 View Post
    Well, each course is only available at one level so that part is easy.

    I tried to set up the relationships as you suggested. I've attached a relationship report. I hope I did it the way you meant?

    Yes, departments are like English, Art, Science, etc. The school I'm at follows the NZ educational system, though, so Grades (as in levels) are called "Forms" and Homerooms are called "Form Classes." I've also used "Marks" to mean Grades (as in scores).

    As for relating marks to the appropriate courses, I was looking at the Northwind database and had an idea. What if I were to treat enrollment like an order? So each order (unique enrollment data group) would include products (classes), order details (marks), a customer (student), and a supplier (teacher). I could then make a form that's filtered by supplier (teacher). This form would only list orders (enrollment records) that contain the selected supplier's products (that teacher's courses). Then orders be used to enter details of each order's products (marks of teacher's courses). Maybe that was written in a confusing way. It would look like this:


    Supplier/Teacher: Smith, John

    Order/Enrollment#....Customer/Student....Product/Course....Details/Marks
    123.......................Galt,John............... .English2..............90%,A,A,B,B,Can do better.
    598.......................Trout, Kilgore...........English2..............98%,A,A,A, A,Great student.
    397.......................Karenina, Anna.........Literature...........97%,A,A,A,A,Keep it up.

    I'm not quite sure how I'd create this form using the tables I have but it seems like it would work. What do you think?

    I would like to know what is the difference between marks and StudentMarks ? why do you separate them?

Posting Permissions

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