Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    recursive relation options...

    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

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    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.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Originally posted by r123456
    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

  4. #4
    Join Date
    Jan 2004
    Posts
    6
    Originally posted by jrk
    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.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •