SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Web Directory

  1. #1
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Web Directory

    Hi

    I've been asked to put together a web directory with a parent>child>sub-child structure. I have no idea how deep the directory tree structure is likely to go, so I figured I'd store my categories in a database table as follows:

    Categories
    categoryid PK
    Categoryname
    Childof

    Example
    1 | Computers & Internet | Childof=0
    2 | Web Design & Development | Childof=1
    3 | Internet Service Provider | Childof=1

    In this example both "Web Design & Development" AND "Internet Service Provider" are sub categories of "Computers & Internet"

    This seemed logical when planning the database, but I'm worried about creating a circular reference within the table. So first Question, in terms of database design is this a nifty idea or just bad form?

    Second Question, assuming its not bad form I need a little help with a sub-query:

    As I read in the parent categories (those with Childof = 0) I also want to select two subcategories to display with them, the result should be presentation a little like the dmoz.org home page.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    yes, it is a perfectly acceptable design

    but instead of Childof=0, you'll want to use NULL (otherwise you will not be able to declare the FK properly)

    regarding your query, which two subqueries would you like to show? you may not answer "any two" or "the first two" -- it must be based n some data value within the subcategory, e.g. the 2 with the earliest date, or something similar
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Sorry for the slow response, thanks for the linked article, I guess I'm using the "Adjacent List Model", didn't know it was called that, but hey guess you learn something new on Site Point every day! I'm gonna stick with it because my tree structure is unlikely to be particularly deep.

    Thanks also to R937 for your reassurance, although I'm unclear about why top level categories should have childof set to null rather than 0, childof is not used as a Foreign Key for another database table, only to enable recursive queries on the category table, I guess in way I'm using it as a foreign key creating a relationship back on its own table. But I'm not sure that's what you meant?

    To answer your question about the sub-query well I was hoping to select the two most popular subcategories, ie: those with the largest number of listings within them. This has to be tolerant of the fact that at this stage there are many categories with no listings.

    My listings table is linked to the Categories table as follows:

    Listings
    ListingID
    SiteID FK -->to SiteID in Sites Table
    CategoryID FK --> to CategoryID in Categories Table.
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SortedSites View Post
    Thanks also to R937 for your reassurance, although I'm unclear about why top level categories should have childof set to null rather than 0, childof is not used as a Foreign Key for another database table, only to enable recursive queries on the category table, I guess in way I'm using it as a foreign key creating a relationship back on its own table. But I'm not sure that's what you meant?
    that is exactly what i meant

    if you were actually to declare childof as a foreign key, as of course you should (why would a foreign key to the same table not be declared along with all the other foreign keys in the database?), then you will find that childof=0 will not work because there is no row with id=0

    and no, it is not okay to have a "super root" with id=0 which all the root nodes can be a child of (i've seen people actually try this, just so that they can keep their application code as is)

    for one thing, if id is an auto_increment, you can't add id=0 anyway
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Oct 2003
    Location
    Tenerife, Spain / UK
    Posts
    329
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK consider it done!
    David Parkes
    Nuclear Internet - Windows Web Hosting
    http://www.nuclearinternet.com


Bookmarks

Posting Permissions

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