Re: What should be the Database Design in this case?
Date: Sat, 7 Dec 2002 19:52:05 +0530
Message-ID: <ast05b$u5sag$1_at_ID-99266.news.dfncis.de>
Thank you Jan for the reply. I'm a bit more clear about normalization and also learnt about another critical ponit to be kept in mind i.e. Foreign key Constraints Complexity. I hope this will help me in screening out the multiple options.
Regards,
Puneet
"Jan Hidders" <hidders_at_hcoss.uia.ac.be> wrote in message
news:3df07152$1_at_news.uia.ac.be...
> 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
Received on Sat Dec 07 2002 - 15:22:05 CET