Search code examples
sqldatabaserelational

Relational Database design to store weekly attendance

I need to design a table to store weekly attendance

Format of the form is :

Todays' Date, Note(if absent for each day) Monday Y/N Tuesday Y/N Wed Y/N Thurs Y/N Fri Y/N

How do I design the table to store such information which is recorded on a weekly bases?

Should it be 1 table having columns as Date , Monday(bit) , MondayNote(varchar) , Tue , TueNote etc upto Fri ?

What is the best approach? TIA

Solution

  • Instead of storing bits for each day of the week, why not just store the actual date with the flag and note? Something like this:

    CREATE TABLE Attendance(AttendanceID int primary key not null identity(1,1), StudentId int not null /* foreign key? */, AttendanceDate date not null, PresenceFlag tinyint not null default(1), Note varchar(max) null);

    So instead of using a single date and five flags for days, which would complicate your logic for getting the actual date, store the actual date for each day of the week. This will simplify a lot of query logic.

    Note that I am using a tinyint instead of bit. This is to allow you to perform arithmetic operations. SUM(PresenceFlag) will give you total a attendance for a period. You can't do this with bit data type. You can use a CONSTRAINT to ensure only 1 and 0 are allowed.

    You can easily get a day of the week from a date using DATENAME(Weekday, AttendanceDate).