Best Practices
-
I am at a junction where my database design will impact the performance of the system. I have to design the database and the fron-end form for taking attendance. I am thinking of denormalizing my database such that each row will have 31 columns of "buckets" for attendance (for a maximum of 31 days in a month). I can then use a DataGrid/GridView to show the data.
This will result in fewer reads. Say I had 10 rows. In a normalized database, I would endup having to read 310 ( 31 x 10 ) as opposed to 10 rows.
Since storage is cheap now, I am not really concerned about the size of the file.
Any suggestions?
-
I'm presuming that you have an integer value for each date.
Given your de-normalised structure, how do you find out the attendance for a month, quarter, year ??
Maybe if you post more details about your schema, you might get some more appropriate feedback. Myself I'd be really interested in the database design where this can impact performance to the point of taking this sort of step.
--------------------
Colt 45 - the original point and click interface
-
The month and the year would be defined as columns.
-
>>This will result in fewer reads. Say I had 10 rows. In a normalized database, I would endup having to read 310 ( 31 x 10 ) as opposed to 10 rows.
False argument, you have different row sizes.
Do you have part time attendance ? What about someone who only attends on Saturdays/weekends ? Do you write 1 large row with a whole bunch of zeroes or Nulls, or just a small number of small rows per month for the part-timer ?
-
Yes, I would have part-timers. But as I mentioned earlier, based on the current cost of the storage, I wouldn't mind writing a large row with a whole bunch of zeros or nulls.
-
Yes, I would have part-timers. But as I mentioned earlier, based on the current cost of the storage, I wouldn't mind writing a large row with a whole bunch of zeros or nulls.
-
Well, your call, but ignoring established normalization rules, and modelling the data according to how it is displayed in a certain presentation layer will nearly always bite you later.
What if the presentation UI requirements change at a later date, and you have to break the display up into weekly sections ? Easy, right, multiple queries on certain of your 1 to 31 columns according to week buckets. But wait, what if a week spans 2 months ? Yikes, now a denormalized table that targetted a month-based presentation looks like the inflexible monster it is.
Model the database correctly and let the data access layer/presentation layer do the job of making it look the way a certain user group wants it to look.
-
I absolutely agree with you. Say, I was thinking of designing my table as follows:
I would design my table as follows:
student_id(pk)
class_id(pk)
month(pk)
year(pk)
day1
day2
day3
day4
.......
day31
And my view would look like:
Class_id: n
day1 day2 day3.....day31
Student_1
----
Student_n
How would you change that?
-
Could be as simple as:
StudentID, ClassID, Date
You don't really need Month/Year, they are easily derived from the actual date. Existence of a record in this table indicates attendance on that day.
Depending on calendar requirements, though, it might be worth constructing a permanent Calendar table, with additional columns like IsWeekend, IsHoliday, and 1 row for every calendar day in a date range applicabel to your app.
LEFT JOINING attendance to this Calendar would be useful for supplying all the days in the period along with an indication of atendance or not.
-
I would consider:
Class Table, Student Table, Attendance table.
The attendance table would house Class_FK, Student_FK, Datetime...or something like that. You can always change the display of data though a report.
Your attendance table requirements are going to vary greatly...as PW said.
-
Also, I heard that SQL 2005/Yukon will have a PIVOT operator. Do you know when this will be released?
-
As far as I know, there is no need to wait. You can still PIVOT in SQL 2000 - just takes more typing.
See Pivot Table in BOL. RH
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply