Re: What should be the Database Design in this case?

In article <aso5dj$shs40$1_at_ID-99266.news.dfncis.de>, Puneet Agarwal <puneet_at_daffodilwoods.com> wrote:
>Hi All!
>
>I've been stuck with this issue and could not find any answers. I am
>designing the database for a School Information System. I've the following
>tables:
>
>
>Course - Stores all the Available Courses
>-----------------
>Id Name
>
>Subjects - Stores all the Available Subjects
>------------
>Id Name
>
>CourseSubjects - Stores the Subjects that are taught
>--------------------------
>Id CourseId SubjectId
>
>Exams - Stores all the Exams that are ...
>--------------------
>Id ExamName
>
>CourseExams - Stores which exams are taken for which ...
>-----------------------
>Id CourseId ExamId
>
>a)
>
>CourseWiseExamSubjectParticulars
>----------------------------------------------------
>CourseId ExamId SubjectId MaxMarks
>
>b)
>
>CourseWiseExamSubjectParticulars
>----------------------------------------------------
>CourseExamId CourseSubjectId MaxMarks
>
>
>I am not sure which one is better. Each of the above has its own pluses and
>minuses.
>
>A)
>+More Normalized
>-Consumes More Database Space
>
>B)
>-Less Normalized (Can reach to Course via both CourseSubjectId and
>CourseExamId)
>-Consumes Less Database Space
>- Easier to ensure data integrity because on those courseExamIds and
>CourseSubjectId's can be mapped which have the same courseId.

Both tables are fully normalized and in 5NF, so that is not the difference. That you can get to CourseId in two ways does not mean there is redundancy because this CourseId is still stored in just one place.

What you should look at (apart from the size of the tables) is the complexity of the foreign keys. In solution a) you get the following foreign keys:

  CourseId, ExamId -> CourseExams.{CourseId, ExamId}   CourseId, SubjectId -> CourseSubjects.{CourseId, SubjectId}

Note that the followsing foreign keys   

  CourseId -> Courses.Id
  ExamId -> Exams.Id
  SubjectId -> Subjects.Id

also hold but follow from the previous two and the foreign key constraints for CourseExams and CourseSubjects, so you don't need to add them separately.

In solution b) you get:

  CourseExamId -> CourseExams.Id
  CourseSubjectId -> CourseSubjects.Id

So you see that the constraints for b) are simpeler and therefore easier for the database to maintain. Think for exampoel of the indexes that have to be defined for that.

So the bottom-line is that solution b) has only advantages wrt. solution a).

• Jan Hidders