#6 Suggestion for changes in structure in 'user' database table
Currently, the courses and sections of a user are
stored in two fields, 'courseID' and 'sections'. These
fields store two parallel comma-separated lists. For
example, a given user's record might have the following
values for the fields:
courseID = '1,1,2,3'
sections = 'A,B,A,D'
This would indicate that the user is part of sections A
and B for the course with an ID of 1. And so on.
If this user were a TA in course 1, the fields would
look like this:
courseID = '1*,1*,2,3'
sections = 'A,B,A,D'
Now the user is a TA in sections A and B of the course
with an ID of 1.
Although this is clunky, it looks like it work pretty
well at first glance. Which it does.
However, what if something screws up somewhere, and
instead of both '1's being marked with an asterisk,
only one was? This would result in erroneous display of
user lists in the user manager (users being displayed
twice, TAs being shown in the students lists, etc.),
and probably many other places I can't even think of
right now. In other words, the current approach leaves
too much space for errors and uncertainty.
My suggestion is to replace the 'courseID' and
'sections' fields with a single field called 'courses'.
This new field would contain all of the information
that the combined 'courseID' and 'sections' fields
currently contain.
For example, for the same user we discussed above, the
value of his (or her) 'courses' field would be:
courses = '1{A,B},2{B},3{D}'
If the user were a TA in the course with an ID of 1,
this would be:
courses = '1*{A,B},2{B},3{D}'
The advantage with this approach is that only one
asterisk needs to be used if the user is a TA in
multiple course sections. Also, the confusion of
parallel lists is removed (I know from experience that
this creates all sorts of headaches) so there is one
less thing to keep in mind when writing code that works
with users and courses. Furthermore the 'courses' field
value is easily parsable with regular expressions.
I realize that this change would entail profound
changes in the structure of the User and Course
objects, which we currently use to store user and
course information.
My proposal is that at some point all other etphp
development be frozen, and this change is implemented
and thoroughly bug tested. Done with care, this
shouldn't be such a big hassle as it may seem.
Markus.
