Re: attendance DB design

From: Bob Hairgrove <rhairgroveNoSpam_at_Pleasebigfoot.com>
Date: Sat, 21 Sep 2002 13:06:44 GMT
Message-ID: <3d8c64d9.14456277_at_news.ch.kpnqwest.net>


On 21 Sep 2002 03:12:17 -0700, nfarheen13_at_yahoo.com (Ms.Farheen) wrote:

>We need to design an attendance module for our college project. The
>basic requirement is to record daily student attendance and generate
>reports based on the dates selected (weekly, monthly). We plan on
>
>a. a table with student id as primary key and a column each for each
>day of the academic year.
>
>however we don't think this is a good database design as the table
>would have 1 column for each day which might come close to 365 columns
>for 365 days.
>
>could anyone please suggest a better alternative or is this approach
>practical.

You are absolutely right. This would be a bad design.

I suggest making a new record only for each day a student is absent, as there will always be less data that way. You need something with only two columns: student id and date of absence.

Try this little schema:

CREATE TABLE Students (

    StudentId LONG NOT NULL,
    StudentName VARCHAR(50),
    CONSTRAINT pk_Students PRIMARY KEY (StudentId));

CREATE TABLE Absentees (

    StudentId LONG NOT NULL,
    Absence DATETIME NOT NULL,
    CONSTRAINT pk_Absentees

       PRIMARY KEY (StudentId, Absence),     CONSTRAINT pk_Absentees

       FOREIGN KEY (StudentId)
       REFERENCES Students (StudentId));

Also, I would set the "cascade deletes" option for the relationship (unfortunately, Access doesn't support the "ON DELETE CASCADE" option in its rather primitive DDL).

Now for some data:

INSERT INTO Students (StudentId, StudentName) VALUES (1, 'Sue');
INSERT INTO Students (StudentId, StudentName) VALUES (2, 'Jim');
INSERT INTO Students (StudentId, StudentName) VALUES (3, 'Mary');
INSERT INTO Students (StudentId, StudentName) VALUES (4, 'Bill');
INSERT INTO Students (StudentId, StudentName) VALUES (5, 'Robert');
INSERT INTO Students (StudentId, StudentName) VALUES (6, 'Helen');
INSERT INTO Students (StudentId, StudentName) VALUES (7, 'Albert');

INSERT INTO Absentees (StudentId, Absence) VALUES (1, #1/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (1, #2/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (1, #3/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (1, #4/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (2, #5/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (2, #6/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (3, #7/1/2002#);
INSERT INTO Absentees (StudentId, Absence) VALUES (6, #8/1/2002#); INSERT INTO Absentees (StudentId, Absence) VALUES (7, #9/1/2002#);

To generate a report with a count of absent days, you make a totals or crosstab query. Here's a totals query which returns a count of absentees grouped by student:

SELECT b.StudentId AS ID, b.StudentName AS Student, COUNT(*) AS Days_Absent
FROM Students AS b INNER JOIN Absentees AS c ON b.StudentId = c.StudentId
GROUP BY b.StudentId, b.StudentName;

As you notice, students with perfect attendance are left out, so we need to join the original table of students with a LEFT JOIN to include all of them, even if they were never absent.

Save the above query as "qTotalAbsent" for the following to work:

SELECT a.StudentName AS Student, Nz(d.Days_Absent,0) AS Days_Absent FROM Students AS a LEFT JOIN qTotalAbsent AS d ON a.StudentId = d.ID ORDER BY d.Days_Absent DESC;

Since the value of "Days_Absent" will be NULL for students which aren't included in the other query, we wrap that in the useful Nz() function to return a 0 instead of NULL.

Note that now it is very easy to use the same table "Absentees" to return totals for each day, week or month by merely grouping the records differently (hint: use the "DatePart" function to group by). This would be extremely difficult to do with the one column per day method.

HTH Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com Received on Sat Sep 21 2002 - 15:06:44 CEST

Original text of this message