-
Database Design Help needed
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
-
Re: Database Design Help needed
"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
-
Database Design Presenation
Here you can find presentation about good database design.Hope it will help you
http://www.stral.in/downloads/Presen...e%20design.pps
Last edited by ph0ton; 04-29-2007 at 08:26 AM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|