CODEWITHSUNDEEP
sqldatabase-design
Tharindu Krishan
Tharindu Krishan

Reputation: 177

Attendance marking database

I'm new to web programming.I try to make an attendance marking system.I have think below method to do that.

Table 

| student_id | 2018_5_8 | 2018_5_9 |
|------------|----------|----------|
|            |          |          |

Every day I'm going to add a column. If student is present value is 1 or otherwise it's 0. Is this a good method or bad? why? Is there any other ways to do that?

Upvotes: 0

Views: 938

Answers (2)

Anthony
Anthony

Reputation: 37065

Just to expand on Gordon Linoff's answer (which should be accepted as the correct one), the benefit of having each date as its own row (instead of as a new column) is that this makes the table easier to query. (It has many other benefits beyond this, but for your purposes, let's start with making queries easier). So following his suggested schema, your table might have data that looks like:

| attendanceid | studentid | attendancedate | mark |
|--------------|-----------|----------------|------|
| 1            | 5         | 2018-05-08     | 1    |
| 2            | 3         | 2018-05-08     | 1    |
| 3            | 5         | 2018-05-09     | 0    |
| 4            | 3         | 2018-05-09     | 1    |

You can now easily see that for any given date whether a specific student was marked present or not. So if you wanted to quickly see what student 5s attendance record was, you could use:

SELECT * FROM attendance
WHERE studentid = 5

Which would return something like:

| attendanceid | studentid | attendancedate | mark |
|--------------|-----------|----------------|------|
| 1            | 5         | 2018-05-08     | 1    |
| 3            | 5         | 2018-05-09     | 0    |

If you wanted to see only the dates that student 5 missed, you could use:

SELECT * FROM attendance
WHERE studentid = 5 AND mark = 0

and get:

| attendanceid | studentid | attendancedate | mark |
|--------------|-----------|----------------|------|
| 3            | 5         | 2018-05-09     | 0    |

Or if you wanted to know the number of days each student has been absent, you could use:

SELECT studentid, COUNT(*) AS missed_days FROM attendance
WHERE mark = 0
GROUP BY studentid

Which, for the example data above, would return:

| studentid | missed_days |
|-----------|-------------|
| 3         | 0           |
| 5         | 1           |

Trying to do any of this with a table having hundreds of columns for each date would be difficult if not impossible.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

This is a very bad approach. In SQL, tables have a fixed set of columns. Adding a new column each day is not recommended.

What you want to do is to have the data in separate rows. I would recommend something like this:

create table attendance (
    attendanceid int primary key,  -- "autoincrement", "serial", or "identity" depending on the database
    studentid int,
    attendancedate date,
    mark int
);

Upvotes: 3

Related Questions