database structure problem - advice please?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Johnson
    #1

    database structure problem - advice please?

    Here's what I was doing, it is obviously stupid because i have exceeded the
    "too many fields defined error"

    I have to create a database driven application that allows students to
    update the records of which classes they took, what grade they received, and
    what semester they took the class in.

    I was using one database table, with some unique identifiers for each
    student. Then for each of the 50 classes they have to take, I had 4 fields
    each, one for whether they took it or not, one for the semester, one for the
    year, one for the grade they received.

    So all in all I had 4x50 fields for the classes and then the various unique
    identifiers.

    I just can't fathom how to simplify this problem. Anybody got any ideas?


    Tags: None
    • Mark C. Stock
      #2
      Re: database structure problem - advice please?


      "Johnson" <johnson@spam.t rollwrote in message
      news:BhS_c.838$ 2H5.165@trndny0 7...
      | Here's what I was doing, it is obviously stupid because i have exceeded
      the
      | "too many fields defined error"
      |
      | I have to create a database driven application that allows students to
      | update the records of which classes they took, what grade they received,
      and
      | what semester they took the class in.
      |
      | I was using one database table, with some unique identifiers for each
      | student. Then for each of the 50 classes they have to take, I had 4
      fields
      | each, one for whether they took it or not, one for the semester, one for
      the
      | year, one for the grade they received.
      |
      | So all in all I had 4x50 fields for the classes and then the various
      unique
      | identifiers.
      |
      | I just can't fathom how to simplify this problem. Anybody got any ideas?
      |
      |

      you seem to be treating the relational database as if it were a big
      spreadsheet

      you need to get at least a rudimentary understanding of data normalization,
      and basic entity/relationship analysis


      get a good book on relational database design (Richard Barker's CASE
      Methodology Data Modelling or David Hayes Patterns in Data Modeling are
      still excellent references)

      you will find that you need many, many more tables, likely:

      STUDENTS -- 'master list' of students, likely with STUDENT_ID as the primary
      key
      CLASSES -- 'master list' of classes, likely with CLASS_ID as PK
      SCHEDULED_CLASS ES -- list of each occurrence of each class, likely with
      CLASS_ID plus CLASS_DATE or a system-assigned sequence number as PK
      STUDENT_CLASSES -- list that relates STUDENTS to STUDENT_SCHEDUL ED_CLASSES

      don't just start creating these tables -- find out why 4 (or more) tables
      are needed by reading up on basic relational database theory

      ++ mcs


        Comment

        Previous template Next
        Working...