I am developing an attendance system for school which will cater to both employees as well as students.
我正在为学校开发一个考勤系统,以满足员工和学生的需求。
The current db schema is
当前的db模式是
attendance table
id - primary key for this table
daydate int(11) - stores timestamp of current day
timing_in varchar(18) - Start time for institution
timing_out - Closing time for institution
status - Status for the day, can be working day - 1 or holiday - 2
Then there are different tables for staff & students, which store the actual attendance values.
然后,工作人员和学生有不同的表格,用于存储实际的出勤值。
For staff, the attendance is stored in attendance_staff. The database schema is
对于员工,出勤率存储在attendance_staff中。数据库架构是
attendance_id - foreign key, references attendance table
staff_id - id of staff member, references staff master table
time_in - stores in timing of a staff member
time_out - stores out timing of a staff member
status - attendance status - can be one among the list, like present, absent, casual leave, half day, late mark, on duty, maternity leave, medical leave etc
For staff, i am storing both present as well as not present entries in the table.
对于工作人员,我在表格中存储现有和不存在的条目。
Now attendance for students has to be included with it.
现在必须包括学生的出勤率。
Since status of each day is already stored in attendance table, can we store not present values for each student in the student attendance table.
由于每天的状态已经存储在考勤表中,我们是否可以在学生考勤表中存储每个学生的不存在的值。
Like, the student attendance table will store only entries for those days who are not present on a particular day.
例如,学生出勤表将仅存储特定日期不存在的那些日子的条目。
The schema for attendance_student will be
attendance_student的架构将是
attendance_id - references attendance table
student_id - references student table
status - will be leave / absent etc other than present.
Will it be efficient to calculate the present days from attendance table using outer join??
使用外部联接从出勤表计算当前天数是否有效?
Thanks in advance.
提前致谢。
1 个解决方案
#1
2
You don't need an outer join to calculate attendance for students. You could simply count the records in your attendance table (one time, since it would be the same for all students) and then just select from your student attendance table to get absences.
您不需要外部联接来计算学生的出勤率。您可以简单地计算出勤表中的记录(一次,因为对所有学生来说都是相同的),然后只需从您的学生出勤表中选择即可获得缺勤。
If you'd prefer to count attendance with an outer join you could. It is likely to be more than efficient enough if you have an index on your attendance table primary key and on the foreign key from student attendance table to your attendance table.
如果您更愿意计算外部联接的出勤率。如果您的考勤表主键和从学生出勤表到您的出勤表的外键上有索引,那么这可能会非常有效。
#1
2
You don't need an outer join to calculate attendance for students. You could simply count the records in your attendance table (one time, since it would be the same for all students) and then just select from your student attendance table to get absences.
您不需要外部联接来计算学生的出勤率。您可以简单地计算出勤表中的记录(一次,因为对所有学生来说都是相同的),然后只需从您的学生出勤表中选择即可获得缺勤。
If you'd prefer to count attendance with an outer join you could. It is likely to be more than efficient enough if you have an index on your attendance table primary key and on the foreign key from student attendance table to your attendance table.
如果您更愿意计算外部联接的出勤率。如果您的考勤表主键和从学生出勤表到您的出勤表的外键上有索引,那么这可能会非常有效。