Data Table Design of php attendance system
sign-in function is a good design for this table. The key is how to figure out how many days a month should be checked in, how many days are normally checked in, how many days are late, and how many days are left early. What more tables do you need to add to design?
is currently a table for the user, checking in a table.
the check-in time and check-out time are stored in the check-in table. Currently, you must check in and check out once a day.
mainly counts how many days you check in normally, how many days you are late, how many days you leave early and how many days you lack your card (and the specific date of missing card) from the 1st of this month to the same day. Clocking in the record database has this good calculation, the key is how to save the missing card record?
this Sunday that needs to be considered, do you want to add another field to store the details of the check-in? how to design this field?
the effect is as follows: and this is real-time statistics, that is, the record from the 1st of the month to the same day.
users have a table ( User ), check-in records a table ( Sign ), and the rest are all algorithms
.
pseudo code:
User(id: int primary key, username: string, sign: array, ...)
Sign(id: int primary key, user_id: int foreign key(User.id), action: bool, date: datetime, ...)
where User.sign is a short integer array equal to the number of days of the month (postgresql may be required to support it. If the array is not supported, it can be stored in a string). For example, 0 means not checking in, 1 means normal check-in, 2 means being late but not leaving early, 3 means not being late but leaving early, 4 means being late and leaving early. Sign.action indicates check-in or check-out (or it can be split into two tables, the check-in table and the check-out table).
each time you check in, update the user's sign field (the algorithm is written on your own).
clear the Sign table at the end of the month, and save this month's check-in record to a file for verification if necessary.
Sign:
id: (uuid),
user_id: (uuid),
sign_date: (date),
sign_time: (time),
Statistics, someone's check-in time on a certain day.
1. :
SELECT COUNT(DISTINCT sign_date) FROM Sign WHERE sign_time < ? AND user_id = ? AND sign_date >= ? AND sign_date <= ? GROUP BY sign_date;
-- ..
2. :
SELECT COUNT(DISTINCT sign_date) FROM Sign WHERE sign_time > ? AND user_id = ? AND sign_date >= ? AND sign_date <= ? GROUP BY sign_date;
-- .,.
3. :
-- 2,,
-- SQL ,,..
