How do I store the student's absence date on SQL Server?
I have a table of students in SQL Server.
In my web application, I have textBox
where I can enter date ( dd-mm-yyyy
). This date means that any (the application that knows for sure) student was absent that day.
How do I store this date in SQL Server?
No problem if a student is only one day away for life, because I can create another column in my student table and store the absence date there.
But I do not know how many days the student will be absent. I can add a thousand columns to the students table and write the absence dates there, but this is a very bad solution.
So how do you store absence dates in SQL?
I have written my web application in ASP.NET, C #, SQL Server.
source to share
You need to create another table named absents with three columns:
- id (main index and auto_increment)
- student_id (must not be unique)
- date
The id column is just the id of the absent one (it's good practice to have an id for every row in the table). Student_id is a reference to the id column of the students table, identifying the correct student. And the date column is the absence date.
Another good practice is to create relationships internally and set triggers for actions like deletion (what if the student is deleted?).
source to share
You need to have another table to keep track of the dates the student was absent from.
Say your current table looks like this:
Student
-------
StudentId [PK]
...
So now you will create another table like:
StudentAbsent
-------
StudentAbsentId [PK]
StudentId [FK, Student.StudentId]
AbsentDate
To get the dates the student has been id=5
missing since , you do in SQL
SELECT AbsentDate FROM StudentAbsent
WHERE StudentId = 5
About and BTW you want to know more about the relationship. If it's a 1-1 relationship , one row table1
is related to one row table2
.
If it is a 1-n relationship , one row table1
is associated with many rows table2
(as in the case above)
If it is an nn relationship , one row table1
is associated with many rows, table2
and vice versa.
source to share