Ranking App - DB Schema help 1

[avu]

Hello,

I need your expert help in creating a db for a Poll Ranking application. The idea is, a school wants to conduct a poll at the end of a schoolyear. There are 20 teachers and 100 classes in total. Each teacher is required to rank these 100 classes. At the end, the poll yields the rank of each class.

Here are the databases I've got:
- tblTeacher: teacherId | teacherName
- tblClass : classId | classDesc

Now, the ranking result will look like:
teacher #1 - rank #1 - class #26
rank #2 - class #53
rank #3 - class #...
....
rank #100 - ...

teacher #2 - rank #1 - class #...
...
rank #100 - ...

Since each teacher is required to rank all 100 classes, there will be 20 (teachers) x 100 (classes) = 2000 ranking records.

I'm not sure how the db schema for the ranking results would be like? I'm thinking:
- tblRank : t1rk1 | t1rk2 | t1rk3 | ... | t1rk100 | t2rk1 | ... | t2rk100 | ... | t100rk100

It means this "tblRank" will have 2000 fields, and each field stores the 'classId'. 't#rk#' = 'teacherId_rankId'

Is this a good schema for 'tblRank'? I'd be a pain if we need to add a new class, #101, or a new teacher #21.

Please help.

Thanks.

allan

 

[SQLSister]

No, no, no never design this way. As you pointed out it is imossible to maintain and it is hard to get resluts from this structure.

TeacherID, ClassID, Rank, Rating Period are all you need in this table. You probably will also want related tables with teacher information and Class information.

YOu will have at the end 2000 records, not 2000 fields.

Then if you want the average rank of a class:
select Avg(Rank) from table1 group by ClassID

This type of thing would be near impoosible to find out from the structure you proposed.