Database Design Help needed

[Brian Pittman]

Hi,



God love your soul to the person who can help me with this design.



I am currently developing a database (SQL 2K, Win 2K) for an educational
association. I have most of it mapped out, but I would like some advice
from people with more experience than me. I am open to all criticism of the
design; so let me walk you all thru the steps.



First I will list all of the Entities and associated fields that are in the
db



1). SCHOOL_LEVEL // This is the top level table

LevelID // Primary key, Identity

LevelName // Char(12) it will never be longer than that



2). SUBJECTS // This tables depends on SCHOOL_LEVEL

SubjectID // Primary key, Identity

SubjectName // Varchar(40)



3). GRADES // depends on SCHOOL_LEVEL and SUBJECTS

GradeID // Primary key, Identity

GradeName // Char(12) it will never be longer than that



4). COURSES // depends on SCHOOL_LEVEL and SUBJECTS (and possibly GRADES
too, not sure though)

CourseID // Primary key, Identity

CourseName // Varchar(40)



5). STRANDS // depends on SUBJECTS

StrandID // Primary key, Identity

StrandName // Varchar(50)



Ok this where stuff starts to get a bit complicated. Each of these tables
are Domain tables only, to be used for looking up information that will not
change.

In the SCHOOL_LEVEL table there are only four values.



Primary

Elementary

Intermediate

High School



In the SUBJECTS table there are 27 Subjects (in no particular order)

For every SCHOOL_LEVEL there are many subjects i.e.



Primary Art

Elementary Art

Intermediate Art

High School Art



So on so forth. The problem with that is as you go up each level of
schooling more subjects are added, for example Intermediate (grades 7-9) has
more subjects than Primary (Kindergarten - Grade 3) does.



So as I see it I now, I will need an associate table for SCHOOL_LEVEL and
SUBJECTS so I can have a many to many relationship between these two tables.
Please correct me if I am wrong.



I'll call this Table SUBJECT_LEVELS with the following structure.



LevelID // INT Foreign Key

SubjectID // INT Foreign Key

I'm also not sure whether I should have a Primary Key field in this table or
not?



Next in the structure is GRADES; it has 13 values from Kindergarten to Level
III (grade 12) in no particular order. Based on the SCHOOL_LEVEL that was
selected GRADES are decided. Again I figure I will need an associate table
to create a many to many relationship and show what GRADES are related to
what SCHOOL_LEVELS.



I'll call this table GRADES_LEVELS with the following structure.



LevelID // INT Foreign Key

GradeID // INT Foreign Key

Again does this table need a Primary Key or not?



Down one more level in the structure is the COURSES table. This table will
only apply when SCHOOL_LEVEL is high school but it is also dependant upon
the SUBJECTS table and GRADES table. For example you pick high school then
you pick your subject, then you pick your grade you are then presented with
your course selections (there are different courses for different high
school levels, grades 10 - 12). There are cases when there are multiple
courses for each subject i.e. Art 1101, Art 1102, Art 1103 Again I figure an
associate table will be needed. I am unsure as how to design this table
though.



Do I create a table and call it LEVELS_SUBJECTS_COURSES with the structure
of

LevelID // INT Foreign Key

SubjectID // INT Foreign Key

CourseID // INT Foreign Key



Or



SUBJECTS_GRADES_COURSES with the structure of

SubjectID // INT Foreign Key

CourseID // INT Foreign Key

GradeID // INT Foreign Key

And join the tables on the LEVELS_SUBJECTS table because the level is pretty
much already known (high school).

Again would this table need a Primary Key? Is there a easier design to this
table?



Last but not least, the STRANDS table, this one is pretty easy. The strand
will only apply to the SUBJECTS table but there will be many STRANDS for
many SUBJECTS i.e. Literature has multiple STRANDS as does art, math etc.
Only problem with this is not every subject has a strand, so would it be ok
to put a value in the STRANDS table that says "No Strand for this Subject"
that has an associated ID with it (A colleague suggested allowing nulls, I
found that scary).

So again I figure I need a many to many relationship for STRANDS and
SUBJECTS

The structure I visualize is as follows.



SUBJECTS_STRANDS

SubjectID // INT Foreign Key

StrandID // INT Foreign Key

Again does this table need a Primary Key?



Am I over complicating things or am I missing a part of the design that I
can't see?

Well as you can see not a simple design. I think that is everything, thanks
for all help in advance.



Brian Pittman

[drew]

"Brian Pittman" <bpittman@roadrunner.nf.net> wrote:
>Hi,
>
>
>
>God love your soul to the person who can help me with this design.
>
>
>
>I am currently developing a database (SQL 2K, Win 2K) for an educational
>association. I have most of it mapped out, but I would like some advice
>from people with more experience than me. I am open to all criticism of
the
>design; so let me walk you all thru the steps.
>
>
>
>First I will list all of the Entities and associated fields that are in
the
>db
>
>
>
>1). SCHOOL_LEVEL // This is the top level table
>
> LevelID // Primary key, Identity
>
> LevelName // Char(12) it will never be longer than that
>
>
>
>2). SUBJECTS // This tables depends on SCHOOL_LEVEL
>
> SubjectID // Primary key, Identity
>
> SubjectName // Varchar(40)
>
>
>
>3). GRADES // depends on SCHOOL_LEVEL and SUBJECTS
>
> GradeID // Primary key, Identity
>
> GradeName // Char(12) it will never be longer than that
>
>
>
>4). COURSES // depends on SCHOOL_LEVEL and SUBJECTS (and possibly GRADES
>too, not sure though)
>
> CourseID // Primary key, Identity
>
> CourseName // Varchar(40)
>
>
>
>5). STRANDS // depends on SUBJECTS
>
> StrandID // Primary key, Identity
>
> StrandName // Varchar(50)
>
>
>
>Ok this where stuff starts to get a bit complicated. Each of these tables
>are Domain tables only, to be used for looking up information that will
not
>change.
>
>In the SCHOOL_LEVEL table there are only four values.
>
>
>
>Primary
>
>Elementary
>
>Intermediate
>
>High School
>
>
>
>In the SUBJECTS table there are 27 Subjects (in no particular order)
>
>For every SCHOOL_LEVEL there are many subjects i.e.
>
>
>
>Primary Art
>
>Elementary Art
>
>Intermediate Art
>
>High School Art
>
>
>
>So on so forth. The problem with that is as you go up each level of
>schooling more subjects are added, for example Intermediate (grades 7-9)
has
>more subjects than Primary (Kindergarten - Grade 3) does.
>
>
>
>So as I see it I now, I will need an associate table for SCHOOL_LEVEL and
>SUBJECTS so I can have a many to many relationship between these two tables.
>Please correct me if I am wrong.
>
>
>
>I'll call this Table SUBJECT_LEVELS with the following structure.
>
>
>
>LevelID // INT Foreign Key
>
>SubjectID // INT Foreign Key
>
>I'm also not sure whether I should have a Primary Key field in this table
or
>not?
>
>
>
>Next in the structure is GRADES; it has 13 values from Kindergarten to Level
>III (grade 12) in no particular order. Based on the SCHOOL_LEVEL that was
>selected GRADES are decided. Again I figure I will need an associate table
>to create a many to many relationship and show what GRADES are related to
>what SCHOOL_LEVELS.
>
>
>
>I'll call this table GRADES_LEVELS with the following structure.
>
>
>
>LevelID // INT Foreign Key
>
>GradeID // INT Foreign Key
>
>Again does this table need a Primary Key or not?
>
>
>
>Down one more level in the structure is the COURSES table. This table will
>only apply when SCHOOL_LEVEL is high school but it is also dependant upon
>the SUBJECTS table and GRADES table. For example you pick high school then
>you pick your subject, then you pick your grade you are then presented with
>your course selections (there are different courses for different high
>school levels, grades 10 - 12). There are cases when there are multiple
>courses for each subject i.e. Art 1101, Art 1102, Art 1103 Again I figure
an
>associate table will be needed. I am unsure as how to design this table
>though.
>
>
>
>Do I create a table and call it LEVELS_SUBJECTS_COURSES with the structure
>of
>
>LevelID // INT Foreign Key
>
>SubjectID // INT Foreign Key
>
>CourseID // INT Foreign Key
>
>
>
>Or
>
>
>
>SUBJECTS_GRADES_COURSES with the structure of
>
>SubjectID // INT Foreign Key
>
>CourseID // INT Foreign Key
>
>GradeID // INT Foreign Key
>
>And join the tables on the LEVELS_SUBJECTS table because the level is pretty
>much already known (high school).
>
>Again would this table need a Primary Key? Is there a easier design to
this
>table?
>
>
>
>Last but not least, the STRANDS table, this one is pretty easy. The strand
>will only apply to the SUBJECTS table but there will be many STRANDS for
>many SUBJECTS i.e. Literature has multiple STRANDS as does art, math etc.
>Only problem with this is not every subject has a strand, so would it be
ok
>to put a value in the STRANDS table that says "No Strand for this Subject"
>that has an associated ID with it (A colleague suggested allowing nulls,
I
>found that scary).
>
>So again I figure I need a many to many relationship for STRANDS and
>SUBJECTS
>
>The structure I visualize is as follows.
>
>
>
>SUBJECTS_STRANDS
>
>SubjectID // INT Foreign Key
>
>StrandID // INT Foreign Key
>
>Again does this table need a Primary Key?
>
>
>
>Am I over complicating things or am I missing a part of the design that
I
>can't see?
>
>Well as you can see not a simple design. I think that is everything, thanks
>for all help in advance.
>
>
>
>Brian Pittman
>
>
>
Okay, I'll take a stab <g>
I think the schema should look like this:

School_Level
ID
Name
HighSchool
Intermediate
Elementary
Primary

Subjects
ID
Name
English
Math
Art

Grades
ID
Name
SchoolLevelID*
K-5
6-8
Freshman
Soph
Junior
Senior

I'd include the key from School_Level to restrict the facts that you can't
be a freshman in Primary for example.

Courses
ID
SubjectID*
Name
Art1101
Art1102
Art1103
Biology101

the rationale here is that we know what subject a course is for, but there
will be a modificaiton to its name or the level of the course, so we take
the SubjectID from Subject (say 3 for Art) and modify its name with 1101

Strands
ID
Name

Associative Entities are:

School_Level_Subjects
School_LevelID
SubjectID

the rationale being that only some subjects are available for each school
level, you can take Advanced Composition in High School, but not in Intermediate
School, so that fact is represented here.

Course_Grades
CourseID
GradeID
the rationale for this table is that Courses are offered only to certain
grades

Strand_Subjects
StrandID
SubjectID

This accomodates the many strands for a subject.

As to your question about primary keys being needed where two foreign keys
are joined, as long as the combination of keys is unique and not null, you
have met the requirement for a primary key. It may be that implementaion
drives you to a surrogate key for each table, just to prefetch one value
for the interface, but this gets in to a religious issue. hope you find this
helpful. my aplolgies if i didnt understand your issues properly
best
drew

[ph0ton]

Here you can find presentation about good database design.Hope it will help you
http://www.stral.in/downloads/Presen...e%20design.pps