Help me choose between two designs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Help me choose between two designs

Author

Topic

bakak
Starting Member

1 Post

Posted - 2008-05-14 : 10:46:20

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

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 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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-14 : 13:15:44

The second design is better as it is more normalized.

Multiple joins are not a problem for SQL Server.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-05-15 : 07:06:22

Second design is by far the best, more normalized and more flexible.

--
Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources