I'm wracking my brain on this one as my database structure-savvyness isn't quite up to par as of yet, so before I make a sad attempt and waste dozens of hours...,I'm here to ask the wise monks to help me get started.

I'm making a link management system which I know upfront I can do 99% of the work. The part that boggles me is how some scripts allow infinite categories (and infinite categories DEEP!). How is it possible to set up infinite levels of categories for each link? 

my $sth = $dbh->prepare(
    "CREATE TABLE IF NOT EXISTS categories
    (
       id int auto_increment NOT NULL,
       url VARCHAR(300) NOT NULL,
       primary key  (id)
    )");
$sth->execute();

[download]
I started going this route and using the URL as a common field to relate to another table. That's fine and dandy, I can give a single url a million different categories this way. But I'm not sure how to make categories have categories of their own, especially since the end user will create the levels via a web form during configuration.

Some sample data: 

Large Animals  -   Small Animals   - Ocean Life
- 4-legged     -   4-legged        - Under 12"
- 2-legged     -   2-legged        - 12" - 3'
               -      *invertebrate
                          *carnivore
                          *herbavore

[download]
As shown above, there are multiple levels of sub categories for the major categories. I am totally confused at where to start. This will be a MySQL database.

My guess is I'd have one table categories for all the first-level categories. Then I could create tables for every single next-level category saving the information of the categories it relates to in each of them. But THAT seems wrong and definitely a waste.

In reply to database table advice by coldfingertips

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.