Mike Day - 2009-01-27

Matty,

Recently I spent an hour reviewing the entire NCC catalog, looking at the requirements of all programs offered to get a feel for the shape of the data we need. Here is the result.

An object relationship diagram is available for viewing at:
http://quickreg.sourceforge.net/forumfiles.php/DAP01.png

This is not in any official diagram notation (UML or otherwise) but should be easy to follow.

Each ellipse is an object class, each of which needs to be represented by its own table, some essential fields of which are listed below the ellipse. If a field has a star (*) before it, then that means it is the primary key. Otherwise, the primary key is implicitly understood to be the class name plus the suffix "Id" (the Program table's primary key is ProgramId, etc.).

An instance of an object is represented by a row in the corresponding table.

The lines are the relationships between objects, labelled at each end according to whether the relationship is one-to-many or many-to-many. For example, the link from Program to ProgReq is one-to-many, because each Program has its own list of multiple requirements, each of which is an instance of ProgReq. Each ProgReq is unique to one Program. On the other hand, all of the relationships between ProgReq, Course, and Group are many-to-many, because a single ProgReq may be fulfilled by one or more courses or by a course from one or more groups of courses, and any given course may be used to fulfill a variety of requirements in a variety of courses, etc. This architecture efficiently provides the flexibility needed to represent almost all of the options and requirements represented by NCC's entire catalog of programs (with some notable exceptions, which I don't think we need to cover for the prototype - ask me if you want to know what I'm referring to).

The standard implementation of many-to-many relationships is elegant and is worth reiterating here: Each such relationship is represented by an auxilary table with NO primary key and only two fields: foreign keys into the two tables the auxiliary table links together. Each entry in the auxiliary table represents a link between a specific instance in each of the two related object classes.

Most of the fields I indentified in the diagram are obvious. SeqId is optional - we don't strictly need it, but it will make the UI prettier and easier to follow. SeqId is just an integer that will allow the UI to sort the corresponding lists in the same order that appears in the catalog, so students using the system will not be confused by a change of order. Semester serves a similar purpose, to divide the requirements/grades into sections just like in the catalog or real-world transcripts.

This architecture does not handle course prerequisites or corequisites, but I believe they will be trivially easy to add in later. I don't think we need to handle them for the very first iteration of the prototype.

What do you think?

As always, feel free to disagree/correct/improve anything above, or to suggest a totally different architecture if you've come up with something you believe is better.

- Mike

[this is a replacement of the previous version of this post - there is a little editing and the link to the diagram has been updated so that the diagram is not shrunken and ugly in small browser windows]