0

I am planning a database that will record school attendance for students everyday.

The problem is that i cant predict the total numbers of student in a class and also the days will increase steadily, so i am struggling with the MySQL db part.

Should i use the students name as columns and each date as rows? I know that using this approach will force me to alter the table's columns every time i add or remove a student from the class. e.g.

day    student1    student2   student3    studentN
----   --------    --------   --------    --------
1        x           x                       x

Have anyone worked on similar problem or have any suggestion?

detola1234
  • 3
  • 3
  • Which database system are you using? MySql or Microsoft SQL Server? You have both tags against your question. – Elliveny Feb 19 '15 at 12:03
  • I don't understand your question. Though what you want is what the databases are for.. Storing multiple data in a once-defined-table. Please clarify a little more. – Sorrel Vesper Feb 19 '15 at 12:09
  • @Elliveny. The db system is MySql. I have removed the sql-server tag – detola1234 Feb 19 '15 at 12:12

1 Answers1

2

The "proper" way to accomplish this using what is called a "normalized" database would be to have a students table and an attendance table

students
--------
id
name

attendance
----------
id
student_id
day

The student_id in the attendance table is just a reference to the unique integer id that each student has in the students table.

Now you can add more student data later, such as address, phone, email, etc and usually split name into first and last name fields. It allows you to update a student name without messing up the attendance data.

Imagine if you store a record for John Smith and later find out his name is spelled Jon instead of John. You would have to update all the individual records that contain his name. Or what about the situation where two students have the same name? That will happen. Using a separate students table with a unique id field will allow for you to encounter these situations without a major reorganization of your data or logic.

davidethell
  • 11,708
  • 6
  • 43
  • 63