Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to create an attendance table for student attendance. Which structure should I go for?

I have two options.

  1. attendance(student_id,date,is_absent,reason)
  2. attendance(student_id,d1,d2,d3,d4,...,d30,d31,is_absent,reason)

Scenario: assuming 100 students in which 50 are absent all 31 days for about 5 months. In case 1 with the assumed scenario the number of total rows is 50 absent * 31days * 5month =7750 At the same time case 2 will create 100 rows irrespective of number of absentees and the respective data is filled if the student is absent. So in total in case 2 100 * 5 months = 500 rows and 500 * 31 cols = 15500 cells (scenario doesn't matter in rows and cols but the number of cells filled is 50 absent * 31 days * 5 month = 7750)

Which one should I go for and would it be fast in querying when I query for attendance report or anything similar? PS: MySQL as database and queries are sent from PHP

share|improve this question
4  
As a starter: if you expect people to spend time with your problem, please try to spend time on phrasing it properly. Check my edit: 'stud' is an English word but have you checked its meaning, for example? Similarly, since characters are cheap nowadays, you had better naming your columns without shortened forms - they only will disturb you or somebody else at a later point. –  dezso Jun 19 '13 at 8:19
    
@dezso, Thank you for the edit I will surely fallow your advise :) Anyone with an answer,view or suggestions to the question please? –  ShaH Jun 19 '13 at 8:26
add comment

2 Answers

There are several disadvantages to using the "month-based row" approach, and I do not see any advantage.

  • Using a row to represent an instance of attendance for a student makes logical sense. Using a row to represent a month's worth of attendance is arbitrary--why a month instead of a year or a 6-week cycle?
  • Think about how you'll be querying this data, or how you'll write a parameterized report for your users--when you're asked to find how many students were absent on a specific day, you'll need to parse out the month to find the right record, then look at different columns depending on the day of the month. Needless complication.
  • Right now, it looks like you want to record whether they are absent and the reason, but that will change eventually. You'll need to add a comment to explain the reason, whether it was approved, or some such, and you don't want to add 31 columns to your table every time that happens.
share|improve this answer
add comment

I would go for the first option, mostly because I believe that less columns are better.

But also:

First of all, the first option would only require an insert when a student is absent, while the second would, first, have an insert for each student at each month, and then for each student that is absent, it would require to update the row each time the student is absent.

The second option would however give a good matrix directly in the database which days a student is absent, but that would be easily achieved through PHP.

Also, having one row per day a student is absent, allows for better statistics when it comes to reporting.

For instance, check how many students that were absent during one month:

select month(date), count(*) from attendance where is_absent = 1 group by month(date);

compared to (here I assume that d1..d31 is an integer with either 1 or 0)

select month(date), sum(d1+d2+d3+d4+d5..+d31) from attendance group by month(date);

Or, if you have a student table as well (which I assume you will have) and you want to see which students were not absent:

select student_name
    from students
    where student_id not in (select student_id from attendance)

Compared to:

select student_name
    from students s 
        join attendance a on s.student_id = a.student_id
    where (d1+d2+d3+d4+d5..+d31) = 0

Finally, since you wanted to store the reason for the student to be absent, it means for option 2 you would either, have one more row for each reason or have one more column for each day which stores the reason, since otherwise you could only store one reason per month, unless you store the reason as a concatenated string in the column which would require a separator and thus limit the characters you put in as reason.

However, in the end it is more of a personal preference, since with correctly created indexes, I doubt that you would have to worry about the performance, but depending on the amount of absent students, option 2 would be using more storage than option 1 (number of columns * rows * size of the data types)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.