Member Avatar for engrjd91

Hey everyone!
I am developing a database for the Attendance system in my university, I've created a blue print somehow and sketched out the entities on the paper. But still I'm a little bit confused about the tables, that might be there should be more number of tables should be available in my database. I'm sharing my concept kindly help me out in filtering my design further.

This System is only for the Attendance for the students in the university, so I have made the following tables in my database.

1. Teachers.
        a)id
        b)name
        c)designation
        d)courses_assigned
2. Students.
        a)id
        b)name
        c)roll_no
        d)courses_registered
        e)Rfid_tag_id
        f)attendance %
        g)registeration_Status
        h)username
        i)password
        j)attendance marked(Boolean)
3. Admin/Users. (Will it be good to make them seperately?)
        a)id
        b)username
        c)password
4. Courses.
        a)id
        b)course_name
        c)credit_hours
        d)teachers_teaching
        e)semester

Is that enough? or some more entity or table should be present in my design?
Can someone help me out regarding to the constraints? or Relationships between the tables?

  • 3 Contributors
  • 2 Replies
  • 202 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by CarterLangley

Dani AI

Generated

Good instinct from to sketch entities first. Two design points need sharpening: never store comma‑separated lists in one column (e.g., courses_assigned or courses_registered) and avoid storing derived values like an attendance % on the students row. Both lead to update anomalies and incorrect data over time. ’s idea to centralize authentication is sensible; that can be refined so authentication (credentials/roles) is separate from profile data. ’s emphasis on referential links is useful—extend that with explicit session and enrollment concepts.

Core practical model to implement (names only): users (for auth + role), students and teachers profiles, courses, course_offerings or sections (course + semester + schedule), enrollments (student ↔ offering), course_teachers (if multiple instructors), class_sessions (individual meeting dates/times), attendance_records (student ↔ session with status, marked_by, marked_at), and rfid_tags (map tag → student with active/disabled flag). Store attendance as one record per student per session and compute percentages from those records on demand (or maintain a denormalized summary table updated by transaction if performance requires it).

Integrity and operational tips: use InnoDB + foreign keys, add unique constraints (e.g., unique roll_no, unique rfid_tag_id, unique (student_id, session_id)), and appropriate indexes on (offering_id, session_id) and (student_id). Never store plaintext passwords—use a strong salted hash (bcrypt/Argon2). Use status values like present/absent/late/excused rather than a single boolean so reports are meaningful. Keep an audit log (who marked/changed attendance and when) and protect against duplicate scans with transactions and the unique constraint.

Example query pattern to compute an attendance rate per student per offering:

SELECT e.student_id,
       SUM(a.status = 'present') / COUNT(*) * 100 AS attendance_pct
FROM enrollments e
JOIN class_sessions s ON s.offering_id = e.offering_id
LEFT JOIN attendance_records a ON a.session_id = s.id AND a.student_id = e.student_id
GROUP BY e.student_id, e.offering_id;

Run schema tests with edge cases (late/retroactive marks, dropped enrollments, tag reuse) before deployment.

Recommended Answers

All 2 Replies

Member Avatar for Alberto Bucur

I have other vision:

Users [id, name, username, password, role]
Field role can take 0 for admin, 1 for teachers, 2 for students

Teachers [id, iduser, designation]
Students [id, iduser, ... other data...]
iduser = id from table Users    

Courses [id, course_name, credit_hours, idteacher, semester]

idteacher - is id from Teachers - the id of teacher who teaching the course; that if a course is teaching by a single teacher otherwise you need a link table like this:
    course_teacher [id, idteacher, idcourse]
and from table Courses eliminate field idteacher

course_student [id, idcourse, idstudent, percent_attendance]
This is a link table becouse a student participate in several courses and one course is followed by more students

attendace [id, idcs, date, attendance marked(Boolean)]
 idcs is the id from table course_student
 or
 [id, idcourse, idstudent, date, attendance marked(Boolean)]

I hope this is help.
Regards.

Member Avatar for CarterLangley 
CREATE TABLE teacher (
    teacher_id INT NOT NULL AUTO_INCREMENT,
    course_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    PRIMARY KEY (teacher_id)
)


CREATE TABLE category (
    category_id INT NOT NULL AUTO_INCREMENT,
    category_id VARCHAR(100) NOT NULL,
    PRIMARY KEY (category_id)
)


CREATE TABLE student (
    student_id INT NOT NULL AUTO_INCREMENT,
    course_id INT NOT NULL,
    rfid_tag_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    roll_no VARCHAR(100) NULL,
    FOREIGN KEY (rfid_tag_id) REFERENCES        rfid_tag(rfid_tag_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id),
    PRIMARY KEY (student_id)
)

Admin

CREATE TABLE admin (
    admin_id INT NOT NULL AUTO_INCREMENT,
    first_name VARCHAR(100) NOT NULL,
    middle_name VARCHAR(100) NULL,
    last_name VARCHAR(100) NULL,
    PRIMARY KEY (admin_id)
)

Courses

CREATE TABLE course (
    course_id INT NOT NULL AUTO_INCREMENT,
    course_name VARCHAR(100) NOT NULL,
    start_date DATE NULL,
    end_date DATE NULL,
    PRIMARY KEY (course_id)
)

Something like this?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.