什么是考勤数据库的良好数据库设计(架构)? [英] What is a good database design (schema) for a attendance database?

问题描述

我正在尝试为亲戚的武术工作室申请出勤.我尝试查找一些类似的示例，但是找不到此类应用程序的任何特定示例或足够清晰的示例.

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.

目前，我使用的是两个表格，一个用于保存学生信息，学生(id，名字，姓氏，电子邮件，...)，另一个用于保存一年中各星期的出勤率，出勤率(id， week_1，week_2，week_3等).我正在尝试更改它以保持几天的出勤率，但是由于我还是MySQL的新手，因此似乎无法想到一个好的方法.

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.

我正在尝试做到这一点，因此有可能以类似于日历的格式查看出勤情况.仅创建365天的列可能会很不好……而且每个月都有一张表也是如此.我注意到一些类似的应用程序只是跟踪日期，并将其存储在数据库中.这种方法会更好吗?或者，还有其他更好的方法来设计这种数据库吗?预先感谢.

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.

推荐答案

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

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和lesson_id 课程表可能是 ID，hold_on_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

除非您需要在课程表中添加更多列，否则我认为这太过分了.

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

这篇关于什么是考勤数据库的良好数据库设计(架构)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！