Home -> Usenet -> comp.databases.theory -> What should be the Database Design in this case?

What should be the Database Design in this case?

From: Puneet Agarwal <puneet_at_daffodilwoods.com>
Date: Thu, 5 Dec 2002 23:50:02 +0530
Message-ID: <aso5dj$shs40$1_at_ID-99266.news.dfncis.de> 





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


----------------

1        IX
2        X


Subjects                        -    Stores all the Available Subjects






Id   Name




1    Maths


2    English


3    Science



CourseSubjects            -              Stores the Subjects that are taught
for a particular course. The data given below indicates Maths & English are
taught in IX

                                                    and English & Science


are taught in Xth.




Id    CouseId    SubjectId



1        1                1
2        1                2
3        2                2
4        2                3


Exams                    -                 Stores all the Exams that are


held in the School




Id        ExamName



1           UnitTest
2            HalfYearly
3            Final

CourseExams        -                Stores which exams are taken for which
courses. The data given below indicates Only HalfYearly and Final exams
                                               are taken for Cousre IX and


HalfYearly and UnitTest are taken for Course X




Id    CourseId ExamId



1            1            2
2            1            3
3            2            2
4            2            1







I hope the above database schema is clear and also properly normalized. Now
I want to store Subject wise MaximumMarks for each course. I have two
possible database designs for that.



a)



CourseWiseExamSubjectParticulars




CourseId        ExamId        SubjectId    MaxMarks



    1                    2                    1                50
    1                    2                    2                50
    2                    1                    3                25







b)



CourseWiseExamSubjectParticulars




CourseExamId    CourseSubjectId     MaxMarks



        1                        1                        50
        1                        2                        50
        4                        4                        25







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.




Any help on this would be highly appreciated.




Thanks in advance



Puneet
Received on Thu Dec 05 2002 - 19:20:02 CET












Original text of this message