Table design for school registration system

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
Hi guys,

I was wondering if someone could help me a bit here. Im trying to
desing an electronic register system for a school. In terms of the
table design, I obviously need a Student Details table with such
attributes as ID, FNAME, SNAME, Age, Tutor Group, Class, Dependant.

The bit i'm having trouble conceptualising is the system needs to be
able to flag up absent students so there would need to be some sort of
relationship with the Student table. To view all the days that a
particular student has been absent what would I need to do? Would
registration attributes such as present/absent and date go in a
separate table?

Thanks a lot.

Re: Table design for school registration system wrote:
Quoted text here. Click to load it

Suggestion: Use only lower case letters in table and column names. You
can use other naming strategy. Like Capital first letter and each new
word starts with a capital letter. But what ever you do, don't mix up
different naming styles. This is just to make the work of the developers
(including you) easier.

Quoted text here. Click to load it

You only need information about absent days in the table if the only
requirement is to get dates when particular student is absent. But be
carefull about requirements. Even one extra requirement can force you to
redesign the database.

Values in such table should be:
id (auto_increment, just to help with possible joins, updates or other
student_id (link to student table)
date ( The date when student was absent )

Now, if you feed in only the absent dates for each student, it is
trivial to make queries to get all absent dates for a student.

If student is absent for a year, you would now have to add something
like 200 rows (or how many school days there are in a year) in the
table. Or teachers need to insert a new row each day. Did you think
about this situation?

Another solution would be to insert the date range when the student was
absent. But would be the queries less trivial when searching dates when
student was absent. As weekends and other holidays are usually not
counted. But what if some particular day of weekend or some particular
holiday is changed to be a school day ( for example at my school we
sometimes had "party" or something at Saturday so that we didn't need to
come to school at Monday, because that was the last day of the semester.

What if a student is absent half a day or one hour, should that be
marked also? Should there be comments why a student was absent? Some
people can have good reasons for that, while others might be just having
fun at school time.

It is not hard to design a database (well I do that for living, so it
can't be that hard ;). But it is nearly impossible to get a perfect set
of requirements where every situation is considered. And without a
perfect set of requirements, the database won't be perfect and it will
cause problems during the implementation time.

But the example I gave you (id, student_id, date) is enough. If it is
okay that user enters each day one by one and only full days need to be

Re: Table design for school registration system

Quoted text here. Click to load it
Two things:
1) talk to people who have to do the work they will define the data needed
2) ischoolad (works on windows w/o modification but needs a bunch of
capitalizations and case issued fixed to work in a Linux environment)  and
openadmin (works straight up in both worlds) are two php based school
administration scripts that you might want to look at. openadmin is north
American based and ischoolad is Philippines based I think.  I have used both
OpenAdmin is easier to setup and use for schools. Ischoolad was easier to
modify for use by a nonprofit with support groups.


Re: Table design for school registration system

I'm guessing that among other things, you want to track student absences by
class, not day. To get you started:

-You want a table Students with student information

-You want a table Courses with course information

-You want  an associative table Attendance. Its primary key is ClassDate +
CourseID + StudentID. It has a column
Attended which takes on the values 'Y'/'N'

Now this a very simplified, but normalized data model..
In the real world courses have sections and are taught by instructors, etc.

Don't plan your database around the types of queries  you have to deal with.
Capture the business rules that the school operates under.

Quoted text here. Click to load it

Site Timeline