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

Closed. This question is opinion-based. It is not currently accepting answers.

---

Want to improve this question? Update the question so it can be answered with facts and citations by editing this post.

Closed 1 year ago.

The community reviewed whether to reopen this question 1 year ago and left it closed:

Original close reason(s) were not resolved

Improve this question

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,开发者_Python百科 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.

---

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.

---

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.

---

Step back a little, you have two types of entities:

• a person [like a student]
• events [like a class]

Think of any entity as something that exists in the real world.

And one relationship

• attendance

A relationship is just that, an association between entities, and often has time data associated with it or other types of measures.

So without thinking too hard, you should have 3 database tables:

• attendee [E]
• class [E]
• attendance [R]

E = entity, R = relationship

If you find yourself duplicating data in one of the entity tables, this is a good sign that this entity requires a "sub-model". In some places this is called "don't repeat yourself" or DRY and for entity relational modeling, this is called "data normalization".

Remember, there's overhead in both time and code to build a more elaborate schema. So consider starting simple [3 tables] and refactoring away redundancy.