lmann2 wrote:

Alright, this is a sql to java list question and I hope it makes sense.  I have a table of courses that have a unique id, code and title and a table called course_prerequisites that has  course_id and course_pre columns that has a many to many relationship to courses.  My question is how do I translate my many-to-many prerequisites into my list of courses?

This would be fairly easy using Hibernate or another ORM, though I'm assuming you're just using JDBC. The simplest solution is probably to select a combination of course IDs and prereq IDs such that you get pairs like this:

CS203 - CS202
CS320 - CS203
CS320 - CS122

From there you can iterate over the results using the ResultSet cursor and add the prerequisite list for each class into that class's entry in your list of courses.

Fair enough. The issue then may be your Course class itself; is the list of prereqs a List<String> or a List<Course>? While the latter is nice from certain standpoints, it will likely result in massive data integrity/consistency issues down the line, not to mention complicating your prereq list population.

If you think about the ResultSet cursor as an iterator over a 2D array, it shouldn't be too difficult to write a loop that will iterate over each entry in the ResultSet from your aforementioned query, find each key/value course/prereq pair, and add each prereq to the List in the corresponding Course entry.

redge wrote:

Fair enough. The issue then may be your Course class itself; is the list of prereqs a List<String> or a List<Course>? While the latter is nice from certain standpoints, it will likely result in massive data integrity/consistency issues down the line, not to mention complicating your prereq list population.

[...]

It's actually the opposite. List<String> is slightly easier to code with JDBC (though it'd be even easier if you just use String), but it may have integrity/consistency issues down the line.

List<Course> is a list of course object references. If you create multiple Course objects for the same course, you are doing extra work without getting the benefits of OO design.

Integrity/consistency issues usually happen on the database side. Because we always want the users to see the most up-to-date information, data only live in the web tier for a very short period of time (mostly in request scope and occasionally in session scope), so there are not many integrity/consistency issues in web tier to begin with. In other words, even if you decide to use multiple copies of the same Course, it's not a big deal as long as you only keep it in request scope.

On the database side, List<Course> corresponds to a table design of course_prerequisites(course_id, prereq_course_id), and List<String> corresponds to course_prerequisites(course_id, prereq_course_code). The problem of using non-primary-key column for foreign key is that a non-primary-key value may change - for example, we may run out of course code and decide to add another digit to it. If that happens, all the foreign key references need to be updated, which can be quite some work (imagine Facebook uses username as foreign key and allows users to change their usernames).

I'm wrong on one account though: List<String> is likely to make things a lot easier rather than just "slightly easier". This is mostly because for this particular application, most of the functional requirements (e.g. add course, edit course, check prerequisites etc.) can be met using just course codes.

As for supporting non-CS courses as prerequisites, we just need to add those courses into the system. There's a reason why foreign key constraint exists. If we allow users to enter any string as course code, there's a good chance that someone will make a typo and create a course that doesn't exist. When it comes prerequisites, there are more difficult things to support, like "Permission by the instructor", or "Senior standing", or "EE101 or CE101". See if you can come up with a sensible design for those.