May 14, 2008 at 8:57 am
I'm currently developing an ASP.NET website which is using SQL Server 2005 and I couldn't decide between two table designs and I hope you can give me your opinions :blush:
The website is for a school and it'll be used to create tests from questions. The teacher will:
1. Select grade (could be multiple selection)
2. Select class
3. Select subject
The thing is that same question could be used for multiple grades.
Example query: "Get me questions of trigonometry of math from grades 7,8,9"
(Names used instead of ID's to make it more clear)
The first design:
[BigRelationsTable]
ID - QuestionID - GradeID - ClassID - SubjectID
1 - Question123 - Grade7 - Math - Trig
2 - Question123 - Grade8 - Math - Trig
3 - Question123 - Grade9 - Math - Trig
This is a simple design but all of the columns will need indexes because all of them will be used for searching and that makes me think about table/index performance.
Second design:
[GradeClassRelations]
ID - Grade - Class
1 - 7 - Math
2 - 8 - Math
3 - 9 - Math
[ClassSubjectRelations]
ID - GradeClassRelationsID - SubjectID
1 - GradeClassRelations1 - Trig
2 - GradeClassRelations2 - Trig
3 - GradeClassRelations3 - Trig
[SubjectQuestionRelations]
ID - ClassSubjectRelationsID - QuestionID
1 - ClassSubjectRelations1 - 1
2 - ClassSubjectRelations2 - 1
3 - ClassSubjectRelations3 - 1
This one is more normalised but this time the need of doing multiple joins makes me wonder.
What do you think? Which one should I use? Or if you have any other suggestions I'm all ears 🙂
May 14, 2008 at 9:34 am
It depends upon the data you store, if the data is less then u can use the first ones,
If you want to index on all the Columns on the table that is equivalent to a Heap table, this will degrade performance.
Once the table is normalised it is more efficient
Cheers
Suji
May 14, 2008 at 9:37 am
When in doubt, normalise. Besides, normalization will help as you expand your project, like say you want to add a table for responses and or possible responses. Provide yourself future flexibility
Marvin Dillard
Senior Consultant
Claraview Inc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply