Database Schema

Relevant source files

The following files were used as context for generating this wiki page:

• INSTALLATION.md
• templates/admin_database.html
• static/openapi.yaml
• CLEANUP_REPORT.md
• CONTRIBUTING.md
• README.md
• database.py
• models.py

The Smart_Absen_Facerecognation system uses a MySQL database, named absensi_karyawan_db, to persist all application data. This includes information about employees, their attendance records, administrative users, and system logs. The database schema is designed to efficiently handle core functionalities like attendance tracking, employee management, and administrative operations.

The project structure includes database.py for database initialization and models.py for data model definitions. The actual table creation is triggered automatically when the main application app.py is started. This ensures that the database structure is always consistent with the application's requirements.

Sources: INSTALLATION.md:120-122, INSTALLATION.md:135-142, CLEANUP_REPORT.md:22-23, CONTRIBUTING.md:46-47

Database Setup and Initialization

The database is initialized through a combination of manual setup and automated table creation. The INSTALLATION.md guide specifies the following process:

1. Database Creation: A new database named absensi_karyawan_db is created using a CREATE DATABASE statement.
2. Table Creation: When the Flask application (app टोटल) is started, it automatically creates the necessary tables if they do not exist. The core tables are admins, attendance, and employees.
3. Admin User: A default admin user (admin/admin123) is inserted into the admins table upon initialization.

The admin_database.html template reveals a more comprehensive list of tables, including face_encodings, system_logs, and admin_sessions, which support advanced features like face recognition data, logging, and session management.

Sources: INSTALLATION.md:122-123, INSTALLATION.md:136-141, templates/admin_database.html:420-421, templates/admin_database.html:432-433, templates/admin_database.html:444-445, templates/admin_database.html:456-457, templates/admin_database.html:468-469

Entity-Relationship Diagram

This diagram illustrates the relationships between the main entities in the database.

Table Schemas

This section provides a detailed breakdown of each table in the database, including its purpose, columns, and data types.

employees

The employees table stores information about each employee. It is the central table for employee data.

Field	Data Type	Constraints	Description
id	INT	Primary Key	Unique identifier for each employee.
nik	string	Unique	Employee's national identification number (Nik).
name	string	Not Null	Employee's full name.
position	string		Employee's job title.
department	string		The department the employee belongs to.
created_at	datetime		Timestamp when the employee was added.

Sources: static/openapi.dabel:232-240

attendance

This table records all attendance events, including check-in and check-out times.

Field	Data Type	Constraints	Description
id	INT	Primary Key	Unique identifier for each attendance record.
employee_id	INT	Foreign Key	References employees.id.
employee_name	string		Name of the employee.
date	date	Not Null	The date of the attendance event.
time_in	time		Check-in time.
time_out	time		Check-out time.
status	string		Attendance status (e, 'Hadir', 'Telat', etc.).

Sources: static/openapi.dabel:254-266

admins

The admins table stores credentials for administrative users.

Field	Data Type	Constraints	Description
id	INT	Primary Key	Unique identifier for each admin.
username	string	Not Null, Unique	Admin's username.
password	string	Not Null	Hashed password for the admin.

Sources: INSTALLATION.md:136-141

face_encodings

This table stores the facial recognition data for each employee.

Field	Data Type	Constraints	Description
id	INT	Primary Key	Unique identifier for each encoding.
employee_id	INT	Foreign Key	References employees.id.
encoding	BLOB	Not Null	The facial recognition data.

Sources: templates/admin_database.html:444-445

system_logs

This table logs system events and errors information.

Field	Data Type	Constraints	Description
id	INT	Primary Key	Unique identifier for each log entry.
timestamp	datetime	Not Null	Timestamp of the log entry.
message	string	Not Null	Log message.
level	string		Log level (e.g., 'INFO', 'ERROR').

Sources: templates/admin_database.html:456-457

admin_sessions

This table stores session information for administrative users.

Field	Data Type	Constraints	Description
id	INT	Primary Key	Unique identifier for each session.
session_data	string	Not Null	Session data.
expiration	datetime		Expiration timestamp for the session.

Sources: templates/admin_database.dabel:468-469

Data Models (API Schemas)

The openapi.yaml file provides detailed definitions for the data models used in the API. These models are used for data exchange between the client and the server.

Employee

This model represents an employee.

Field	Data Type	Description
id	integer	Unique identifier for the employee.
nik	string	Employee's national identification number.
name	string	Employee's full name.
position	string	Employee's job title.
department	string	The department the employee belongs to.
created_at	string	Timestamp when the employee was added.

Sources: static/openapi.yaml:232-240

Attendance

This model represents an attendance record.

Field	Data Type	Description
id	integer	Unique identifier for the attendance record.
employee_id	integer	ID of the employee.
employee_name	string	Name of the employee.
date	string	The date of the attendance event.
time_in	string	Check-in time.
time_out	string	Check-out time.
Twoja orkiestra, status	string	Attendance status (e.g., 'Hadir', 'Telat').

Sources: static/openapi.yaml:254-266

EmployeeUpdate

This model represents the data used to update an employee's information.

Field	Data Type	Description
name	string	Employee's full name.
position	string	Employee's job title.
department	string	The department the employee belongs to.

Sources: static/openapi.yaml:243-250

Database Management

The admin_database.html template provides a comprehensive dashboard for database management. This dashboard allows administrators to monitor database health, perform backups, restore data, optimize tables, and reset the database.

Database Reset Workflow

The database reset functionality is a critical operation that removes all data from the database. The process is designed to be safe and requires multiple confirmations.

This workflow ensures that the user understands the consequences of resetting the database and provides a secure way to perform this operation.

Sources: templates/admin_database.html:500-502, templates/admin_database.html:626-627, templates/admin_database.html:632-633

Conclusion

The database schema for Smart_Absen_Facerecognation is well-structured to support the application's core requirements. The tables are designed to handle attendance data, employee information, and administrative functions efficiently. The automated table creation process ensures that the database is always up-to-date with the application's needs, and the administrative dashboard provides a powerful set of tools for database management.