在数据库中存储考勤记录 [英] Storing attendance records in the database

问题描述

我发现了一个类似的问题（学校出勤数据库）

I found a similar question asked previously (School attendance database)

我必须处理这些附加条件。

I have to deal with these additional conditions.

1. 出席人数为10万。


2. 每个用户都将有滑动输入滑动输入。


3. 用户可以进行多次滑入/滑出操作，


4. 必须维持1年的出席记录，可供用户访问。

1. Total number of users recording attendance would be 100,000.
2. Each user will have swipe-in swipe-out entry.
3. A user may do multiple swipe-in swipe-out incase s/he is not sure data was captured.
4. A record of 1 year attendance has to be maintained which can be access by the user.

我想到的基本表格是以下条目。

The basic table i thought was with following entries.

1. UserID - 数值


2. 日期


3. 快速滑动


4. 滑动逾时。

1. UserID - numeric value
2. Date
3. Swipe in time
4. Swipe out-time.

如果这是表，那么在理想情况下，数据库中的大约行数将为= 100,000×250（工作日，年）= 25,000,000。现在，如果用户复制，则刷入或刷出行将累加。说1/3的员工这样做，以确保出席率被标记。所以额外的行8,333,333总计33,333,333约。

If this is the table then approx number of rows in database would be = 100,000 x 250(working days in yr) = 25,000,000 in ideal situation. Now if user duplicate either swipe-in or swipe-out rows will add up. Say 1/3 of employee do this to ensure attendance is marked. so additional rows 8,333,333 totalling to 33,333,333 approx.

其中一个问题是当用户刷入两次，但只刷出一次。然后，我需要在第二次刷入空值或填充相同的值在刷出字段。这将添加所提到的额外行。
另一个选项，我想是每天运行一个后台任务来清理双用户条目。假设用户在上午8点00分，然后上午8点10秒刷卡，所以系统会在一天结束时删除上午8.10的条目。首先退出时间。

One of the issues would be when a user swipes-in twice but swipes out only once. Then i need to have null value in the second swipe-in or fill the same value in the swipe-out field. This would add up the additional rows mentioned. The other option i thought was to run a background task every day to clean the double user entry. Say user swipes in at 8.00 A.M and then 8.10 A.M so the system removes the 8.10 A.M entry at the end of the day.First in last out time basis.

我知道。如果说用户在办公室工作过夜，刷卡可能是2.00 A.M.滑动数据为

However, i prob i forsee is. If say user stays overnight in office working and swipes maybe 2.00 A.M. The swipe data would be

1. 滑动 - 1-Jan-10 - 8.00 AM


2. out - 2-Jan-10 - 2.00 AM


3. 滑动 - 1月2日 - 下午1.00点（他同一天再次回到办公室 - 工作压力：））


4. 滑动 - 1月2日 - 10:00。
   如何处理这个？

1. Swipe in - 1-Jan-10 - 8.00 A.M.
2. Swipe out - 2-Jan-10 - 2.00 A.M.
3. Swipe in - 2 Jan-10 - 1.00 P.M. (he comes back to office again same day - work pressure :))
4. Swipe out - 2 Jan-10 - 10.00 P.M. How to handle this?

我的问题是：
1.列出的行数是否可接受数据库像mysql，postgresql没有延迟太多的retrival时间？我会更感兴趣的在开源数据库性能。
2.是否有比这更好的格式化表格的方法？

My questions are: 1. Is the number of rows listed acceptable to databases like mysql, postgresql without delaying too much of retrival time? I would be interested more in opensource db performance. 2. Is there a better way to format the table than this?

推荐答案

刷卡而不是天，然后后处理数据以实现所需的跟踪 - 即使没有你的例子，有更多更基本的出去吃午饭的情况或其他原因离开网站，需要多个到达和离开每天。

The simple answer is that you log swipes rather than days and then post-process the data to achieve the required tracking - even without your example there are the more more basic cases of "going out for lunch" or other reasons to go off site that require more than one arrival and departure per day.

无论你做什么，你会遇到多次刷卡的问题 - 人们是人，你将与边缘情况下斗争，即用户在一个奇怪的时尚无论什么原因（通常很无辜...）。

Whatever you do you're going to have issues with multiple swipes - people being "people" you're going to struggle with edge cases i.e. where a user behaves in an odd fashion for whatever reason (usually quite innocent...).

这篇关于在数据库中存储考勤记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！