Hello,
New to this forum...was hoping for some new ideas/options on a simple issue (I think) with regards to recursive relations. Want to design a normalized relationship between what is now designed as one recursive table. The COURSE_PREREQ table below is currently designed to hold all course prerequisites for each course defined in the COURSE table. I'm thinking there might be a better way, and was hoping for some input on this. Apreciate any thoughts:

COURSE
course.course_id
course.course_type_id
course.course_code
course.course_title
course.course_desc
course.course_suggested_min
course.course_suggested_max

COURSE_PREREQ
course_prereq.course_prereq_id
course_prereq.course_id
course_prereq.course_prereq_id

Thanks for your time,
JRK

Assuming the relationship is M:N,

PRE_REQ
Course_ID
Course_PRE_REQ_ID
Primary Key (Course_ID, Course_PRE_REQ_ID)
Foreign Key Course_ID References Course(Course_ID)
Foreign Key Course_PRE_REQ_ID References Course(Course_ID)

An additional constraint would be needed to enforce Course_ID != Course_PRE_REQ_ID.

Wow...very fast reply...sincerely appreciate that. Is there a good discussion on handling recursive relationships efficiently that you could point me to?...maybe a good book, or article on the Web.

Thanks again,
Randy

Recursive structures can often be viewed as trees, or more generally as a graph structure. For storage and manipultation of graph structures within current RDBMSs you will want to search for information on the two primary approaches: nested sets & adjacency lists.

Joe Celco, who I would typically avoid, has a 3 part article on nested sets that can be linked to from http://mark.stosberg.com/Bookmarks/D..._on_Tree_data/

From these articles and searches you should find many discussions of different methods for implementing these structures through procedures and triggers.

With the lack of an explosion operator in ANSI SQL you could also try to manually maintain an explosion table.

Hope this is what you were looking for.

joe celko probably forgot more about sql than you will ever know, mister or miss gallon

avoid him if you want to, but hey, the rest of us do appreciate what he has done for the field

i have 3 of his books and his sql is impeccable

where's yours?