ASP.NET and a One-to-Many-to-Many Scenario

Question

I'm new to ASP.NET but not to programming. I am migrating our current site from PHP/MySQL to ASP.NET(3.5)/SqlServer. I've been lurking here since the site's launch, so I'm confident that one (or more) of you can help me out. Here's the scenario:

This is a training department site and the dept. has a course catalog stored in the table course. Each course may have many prerequisite courses, For example, A and B are prerequisites for C. I would normally store this either as a comma-delimited column in course or in a separate table course_prereq or course_course as a recursive relationship. This part I can do.

However, the business rules require that each course can have multiple sets of prerequisites. Fore example, N requires A, B and C, or N requires X and Y. This is where I'm stuck.

Previously, I stored this information in a column for row N as A,B,C|X,Y, parsed the ids into a PHP 2D-array, submitted a second query for all the rows whose id was in that array, then used PHP to separate those rows into their respective groups. Once all this processing is done, the groups of prerequisites are displayed as separate tables on the web page, like so:

| A | A's information goes here |
| B | B's information goes here |
| C | C's information goes here |
- - - - - - - OR - - - - - - - -
| X | X's information goes here |
| Y | Y's information goes here |

How would I accomplish this using ASP.NET?

Answer 1

Add a table to hold Prerequisite Sets. This table holds a set ID and key back to the courses table for each course in the set. The table may have several rows for a given set ID, so your primary key will be the set ID plus the course ID. Then in your course_prereq table you relate courses to the different prerequisite sets. An OR relationship can be assumed there because any ANDs are enforced in the sets themsevles.

Answer 2

Have a table called PrerequisiteSet that FKs to each prereq. Then have a Course_PrerequisiteSet many to many table that FKs to Course and PrerequisiteSet. Most of the time there will only be one entry in Course_PrerequistieSet, but if there are more than one, then it will be an OR relationship.

Answer 3

Both the answers above were very helpful. I ended up using just one database table instead of the suggested two. The table contains a course_id, prereq_id, and set_id, which all together form the primary key.

In the ASP.NET page, I use a repeater to loop over the sqldatasource stored procedure that returns a course's prerequisite sets, and a gridview inside that repeater that reads the individual prerequisite information from a second sqldatasource stored procedure. Like this:

RepeaterSqlDataSource (returns set ids)
Repeater
. . . GridViewSqlDataSource (returns course info for each prereq_id in set
. . . GridView

Hope this is helpful to anyone else looking at a similar scenario.