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