469,087 Members | 1,270 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,087 developers. It's quick & easy.

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?
Jul 19 '05 #1
1 2102

"Johnson" <jo*****@spam.troll> wrote in message
news:BhS_c.838$2H5.165@trndny07...
| 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_CLASSES -- 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_SCHEDULED_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
Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Bob Bedford | last post: by
1 post views Thread by Nathan Bloomfield | last post: by
2 posts views Thread by Shwetabh | last post: by
14 posts views Thread by Mikee Freedom | last post: by
10 posts views Thread by Fenix [Youth Director] | last post: by
9 posts views Thread by Peter Duniho | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.