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

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.

目前，我正在使用两个表，一个用于保存学生信息，一个用于学生（id，first_name，last_name，email，...），另一个用于一年中出勤的表，出勤率（id，week_1，week_2） ，week_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天的列可能会很糟糕......而且每个月都有一个表。我注意到一些类似的应用程序只是跟踪日期，并将其存储在数据库中。这种方法会更好吗？或者，还有其他更好的方法来设计这种数据库吗？提前致谢。

3 个解决方案

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

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

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和lesson_id，课程表可以是id，held_on_date

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

除非你需要在课程表中添加更多列，否则我认为这是过度的。

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

在武术中，教师也是学生 - 因此教师表被子类型化为学生表。所有公共字段都在Student表中，并且只有教师特定的列位于Instructor表中。

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.

ClassSchedule描述了学校提供的已发布课程安排。

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.

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

One row in the TimeTable is 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.

希望这能让你开始。

#3

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:

所以不要太费力，你应该有3个数据库表：

• attendee [E]
• 与会者[E]
• class [E]
• 班级[E]
• attendance [R]
• 出勤率[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个表]并重构冗余。