Database Design Help needed


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Database Design Help needed

Hybrid View

  1. #1
    Brian Pittman Guest

    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




  2. #2
    drew Guest

    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

  3. #3
    Join Date
    Apr 2007
    Posts
    1

    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 09: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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center