MySQL 考勤数据库的良好数据库设计（架构）是什么？

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

提问by Daniel Shin

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.

目前，我正在使用两个表，一个用于保存学生信息，students(id, first_name, last_name, email, ...)，另一个表用于按一年中的周数出勤，出席 (id, week_1, week_2 ，第 3 周，...）。我试图改变它以保持几天的出勤率，但似乎想不出一个好的方法，因为我对 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.

我正在努力使其能够以类似日历的格式查看出勤情况。只制作 365 天的列可能会很糟糕……每个月都有一张表格也是如此。我注意到一些类似的应用程序只是跟踪日期，并将其存储在数据库中。这种方法会更好吗？或者，是否有其他更好的方法来设计这种数据库？提前致谢。

采纳答案by Marco

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.

出勤应该有 id、student_id 和 date。这是您在学生参加时需要记录的全部内容。如果您想知道在特定日期（以及谁）有多少学生参加，您可以针对该特定日期或日期范围运行查询。

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

您还可以创建一个课程表，在这种情况下，出勤表将是 id、student_id 和课程_id 课程表可以是 id、hold_on_date

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

除非您需要在课程表中添加更多列，否则我认为这是矫枉过正。

回答by Damir Sudarevic

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

在武术中，教练也是学生——所以Instructor表格被子类型化到Student表格。所有常见字段都在Student表中，只有教师特定的列在Instructor表中。

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

该Art表列出了学校提供的艺术（柔道、空手道......）。

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

学校可能有几个房间，这些都列在Room表中。

ClassScheduledescribes the published schedule of classes that the school offers.

ClassSchedule描述学校提供的已发布课程时间表。

Attendance is captured in the Attendancetable.

出勤记录在Attendance表中。

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

Calendar表中的一行是一个日历日（日期）。该表具有日期属性，如DayOfWeek，MonthName，MonthNumberInYear等。

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

中的一行TimeTable是一天中的一分钟，例如 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.

希望这能让你开始。

回答by Bret Weinraub

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 timedata associated with it or other types of measures.

关系就是实体之间的关联，并且通常具有与之关联的时间数据或其他类型的度量。

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

所以不用想太多，你应该有 3 个数据库表：

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

• 参加者 [E]
• [E]级
• 出勤率 [R]

E = entity, R = relationship

E = 实体，R = 关系

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".

如果您发现自己在其中一个实体表中复制数据，这是一个好兆头，表明该实体需要“子模型”。在某些地方，这称为“不要重复自己”或 DRY，对于实体关系建模，这称为“数据规范化”。

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.

请记住，构建更精细的模式需要时间和代码开销。所以考虑开始简单的 [3 个表] 并重构掉冗余。