What is a good database design (schema) for a attendance database?

Question

Hi, I'm trying to make a application for keeping attendance for a relative's martial arts studio. I've tried looking around for some similar examples, but I couldn't find any specific or clear enough ones for this kind of application.

At the moment, I am using two tables, one for keeping student information, students(id, first_name, last_name, email, ...), and another table for attendance by the weeks in a year, attendance(id, week_1, week_2, week_3, ...). I am trying to change it to keep attendance by days instead, but can't seem to think of a good approach since I'm still kind of new to MySQL.

I am trying to make it so it is possible to see the attendance in a calendar-like format. It probably would be bad to just make columns for 365 days... and same with having a table for each month. I've noticed some similar applications just keep track of the dates, and store that in the database. Would this approach be better? Or, is there some other better approach to designing this kind of database? Thanks in advance.

Answer 1

Attendance should have id, student_id and date. This is all you need to record when students attended. if you want to know how many students attended on a specific date (and who) you run a query for that specific date or date range.

You could also create a lesson table, in which case the attendance table would be id, student_id and lesson_id the lesson table could be id, held_on_date

unless you need to add more columns to the lesson table, I think it is overkill.

Answer 2

In martial arts, instructors are students too -- so the Instructor table is sub-typed to the Student table. All common fields are in the Student table and only columns specific to instructors are in the Instructor table.

The Art table has list of arts that the school offers (judo, karate ...).

The school may have several rooms, these are listed in the Room table.

ClassSchedule describes the published schedule of classes that the school offers.

Attendance is captured in the Attendance table.

One row in the Calendar table is one calendar day (date). The table has date-properties like DayOfWeek, MonthName, MonthNumberInYear etc.

One row in the TimeTable is one minute of a day, like 7:05.

Calendar and TimeTable allow for easy attendance reporting by date/time, for example

-- Attendance of judo morning classes
-- for the first three months of the year 2010
-- by day of a week (Sun, Mon, Tue, ..)
select
    DayOfWeek
  , count(1) as Students
from ClassSchedule as a
join Calendar      as b on b.CalendarId = a.CalendarId
join TimeTable     as c on c.TimeID     = a.StartTimeId
join Attendance    as d on d.ClassId    = a.ClassID
join Art           as e on e.ArtId      = a.ArtID
where ArtName = 'judo'
  and Year    = 2010
  and MonthNumberInYear between 1 and 3
  and PartOfDay = 'morning'
group by DayOfWeek ;

Hope this gets you started.