sh*************@gmail.com wrote:
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.
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.
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?
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
queries)
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
added.
