No of fields in a table

[GeorgeMar]

I have always wondered about the efficiency of a table
with too many fields.

I have a table of student academic records and it has
grown to 100 fields for each student. The fields have a
one to one relationship with the StudentID, so I don't see
the need to split the table and the associated problems
with updating the various tables.

Should the table be split? What is the acceptable number
of fields before it becomes inefficient?

many thanks
George

 

[Allen Browne]

The problem is not with the number of fields, but the fact that your
non-normalized data structure will severely limit the kind of questions you
can ask your database.

For example, if you have fields to store the students results named:
Maths, English, Geography, ...
just getting the student's grades for a year is a hassle, let alone running
comparisons to build student profiles.

 

[John Vinson]

I have always wondered about the efficiency of a table
with too many fields.

I have a table of student academic records and it has
grown to 100 fields for each student. The fields have a
one to one relationship with the StudentID, so I don't see
the need to split the table and the associated problems
with updating the various tables.

Should the table be split? What is the acceptable number
of fields before it becomes inefficient?

many thanks
George

It has little to do with efficiency: the problem is logical design. If
each "field" is (say) a class, then you have a many to many
relationship embedded within each record in your table. This makes it
much more difficult to work flexibly with the data!

Could you describe what some of these hundred fields are? I strongly
suspect that pulling out many of them into one or more related tables
will give you a much better and more stable design. Just the fact that
you use "has grown" is a big red flag - table structures should be
pretty much stable once the application goes into production!

 

[GeorgeMar]

Thank you both.This has helped.

The tables are neatly broken into Students, Subjects,
Marks etc. It is the Marks table that is so big. It
contains marks for each assessment during a term.

That is, for each subject taken by the student, there is a
maximum of 10 assessments with about 10 marks and grades
for each one.

many thanks
george

 

[Allen Browne]

Okay, thanks for clarifying.

The table does need to be broken down into a related one with fields.

The tables will be something like this:
- Students: one record for each student
- Subject: one record for each subject unit you offer
- SubjectInstance: one record for each time a subject is offered.
- Enrolment: one record for each Student in a SubjectInstance.
- SubjectInstanceAssignment: one record for each assignment in a
SubjectInstance.
- StudentAssignment: one record for each assignment submitted by a
student for a SubjectInstanceAssignment.

The crucial aspect is that, over time, you will offer a subject more than
once. Therefore, a student does not enrol in a subject, but rather a
particular instance of the subject ("The one that started in February 2004,
and was supervised by Mrs Huddlestone").

The assignments can change over time, so they relate to the SubjectInstance.
One SubjectInstance can have many assignments, so the list of assignments
required for a particular instance of the subject is in table
SubjectInstanceAssignment.

Further, a student may need to submit an assignment more than once, so there
may be a one-to-many relationship between StudentInstanceAssignment (the
assignments to be done) and StudentAssignment (the actual submission of an
assignment by a student).

With this arrangement, the StudentAssignment table will contain fields:
- SubjectInstanceAssignmentID which assignment was submitted;
- StudentID who submitted the assigment
- SubmitDate when it was submitted
- DueDate needed if you sometimes grant extensions for individual
students.
- IsRejected Yes/No. Indicates a resubmit.
- Marks results of the assignment.

That brings the table down to 6 fields. Hope that helps.

 

[John Vinson]

That is, for each subject taken by the student, there is a
maximum of 10 assessments with about 10 marks and grades
for each one.

Just to add to Allen's excellent analysis: this is a *perfect*
description of a one to many relationship: One subject to (zero to
ten) assessments. Storing the ten assessments in ten fields is just as
I said - embedding a one to many relationship within a single record!