I am designing an ASP.NET app that can be used to keep track ofattendance at office hours for a class. The purpose of this isthat we need to know if a student is attending office hours bydifferent people (so that we can flag them as "in trouble"). Idon't know if I have chosen the best database design, and I'm lost asto how to accomplish a query I need.
I have a table HoursAttendance that has the following design.
Column_Name Data_Type Length Allow_Nulls
TA char 4 n
Date smalldatetime 4 n
Start smallint 2 n
End smallint 2 n
Student1 bit 1 y
Student2 bit 1 y
Student3 bit 1 y
Student4 bit 1 y
Student5 bit 1 y
I chose to have the students as columns because the students don'tchange, and then you add rows of office hours. If students arethe rows, then you would be adding columns as the semester continuedwhich I thought was odder...? I'm completely open to suggestionson Database Design, because I really wasn't sure.
Ok, so now I need useful queries. The one that I am stuck on (andalso the first one besides select * from HoursAttendance) is that Iwant the names of Students who have attended more than x officehours. So I need something like
select <column name> from HoursAttendance where count(sum(<column name>)) > x
Granted a better table design could help with this. I'mrelatively new to design, so constructive criticism is desired please
![Smile [:)]](http://pics.10026.com/?src=/emoticons/emotion-1.gif)
You can't run a query like that if the students are columns. My suggestions:
Students
---
StudentID int
StudentLastName varchar(25)
StudentFirstName varchar(25)
HoursAttendance
------
HoursAttendanceID int
StudentID int
TA char(4)
AttendanceDate smalldatetime
Hours tinyint
Select StudentID, Sum(Hours)
From HoursAttendance
Group By StudentID
Having Sum(Hours) > x
Hope that helps!
Marcie
|||This is why I didn't go with something like what you suggested: It seems with the HoursAttendance table set up like that that it should be broken into an Hours table and an Attendance table.
This seems like way too many tables lol!!
Oh well, I guess.|||Could be -- what would you put in the Hours table vs. the Attendance table? For the most part, trying to cram everything into one table leads to a poor database design (and is not normalized).
|
| This seems like way too many tables lol!! |
Scoff...at work I work on a system with several hundred tables, this is nothing!
Marcie|||
There are 9 TA's that hold weekly office hours. I have a TA table and a DefaultHours table. The DefaultHours table has when the TA normally holds hours (I use this to set selectedValue in time drop down lists. 5-20 students attend each office hour. So that means there are 5-20 rows that have TA, Date, Start, End in common. Which is where I was referring to moving that to a separate table. *shrug*
|||Sure, you could put that in a separate table then:
OfficeHours
----
OfficeHourID int
TA char(4)
Date smalldatetime
Start int
End int
Attendance
----
OfficeHourID
StudentID
Marcie|||I'm just not sure as to what would be the "best" (most efficient in terms of performance and space)
|||Having the extra tables *saves* space because data isn't repeated in any one table. (Normalization) The performance difference between having one HoursAttendance table and splitting them into two would not even be measurable.
Marcie
