Storing attendance data in SQL [closed]

Question

Bit of a carry on from this question here: Database design for school attendance system

I am currently doing something similar but have a few additional questions. So the school has approx 200 students but this is constantly changing. They attend for roughly 200 days and its required to keep the attendance data for 7 years. This is my create table statement here:

CREATE TABLE attendance (
    studentID INTEGER REFERENCES students (studentID),
    date      DATE,
    present   BOOLEAN
);

My question is, is this the best way to create the table?

I haven't really done any direct database stuff from a UI before so my proposed solution was:

First a local variable that would use C# DateTime.Today function to get todays date; Another local variable which would get the StudentID from the UI; And then the attendance value which is a boolean, again set by the UI and stored as a 0/1;

All of these then combined into the Insert into SQL statement.

Is this the best way? Any tips or help would be appreciated thanks.

Answer 1

I'd propose this table instead:

CREATE TABLE StudentAttendance (
    StudentId int  NOT NULL REFERENCES Students (StudentId),
    Date      date NOT NULL,

    PRIMARY KEY( StudentId, Date )
)

• I don't feel there is a need for a present column because the presence, or lack of, a row can indicate if a Student was present that day or not.
• The table has a composite key comprised of StudentId + Date - this means there cannot be duplicate records (i.e. only 1 row per student per day)
• "Id" is an abbreviation for Identity, it is not an initialism so it should not be all-uppercase (i.e. use Id instead of ID).

Update

After having read the comments by other users, especially @JNevill, I recognise that you probably would want to store student absence reasons, which means you will need an IsPresent bit column (as a row could now mean an is-present or is-absent) with a Notes nvarchar(max) column, but we can extend this to a tinyint to store an enum value for different types of attendance records:

enum StudentAttendanceType
{
    Present = 0,
    UnauthorisedAbsence = 1,
    AuthorisedAbsence = 2,
    MedicalLeave = 3,
    // etc...
}

CREATE TABLE StudentAttendance (
    StudentId int           NOT NULL REFERENCES Students (StudentId),
    Date      date          NOT NULL,
    Type      tinyint       NOT NULL,
    Notes     nvarchar(max) NOT NULL

    PRIMARY KEY( StudentId, Date )
)