The database structure for a tree of data is pretty easy - navigating that tree can be tricky. All you need is for each category to have a parent_id field, something like the following (untested) postgres DDL: 
CREATE SEQUENCE category_ids;
CREATE TABLE categories
    (
       category_id int NOT NULL default nextval('category_ids'),
       parent_category_id int
    );

CREATE SEQUENCE link_ids;
CREATE TABLE links
    (
       link_id int NOT NULL default nextval('link_ids'),
       category_id int REFERENCES categories,
       url VARCHAR,
       description VARCHAR
    );

[download]
Hopefully you can see how that allows for a tree of categories like: 
      Animals{1}
    /            \
Mammals{2}     Invertibrates{3}
  /      \
Cows{4}  Humans{5}
If the numbers in curly braces are the category_ids, then, for example, the parent_category_id of Mammals is 1 and that of Humans is 2.

This maps the tree perfectly, but determining whether, for example, a Human is an Animal but not an Invertibrate is tricky. Oracle has some nice tree extensions to SQL that I have used. I think Postgres does too these days. You can also get an RDBMS neutral implementation by adding a sort column where you store binary sort data. Implementing such a beast is an exercise left for the reader ;)

Update: what you're looking for in Oracle is "CONNECT BY". SQL 99 has "recursive" query support, but Postgres (and presumably MySQL) don't support it. For an excellent discussion of hierarchical data storage and retrieval in SQL see http://www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm - for a description of a Postgres implementation see http://openacs.org/forums/message-view?message_id=16799

Update2: The queries in the link posted from mysql.com that use a single parent id look to me like they would slow down significantly for each nesting level you add - and some of their queries enforce a maximum depth. There's only so many times you can type "LEFT JOIN"! Their idea using left and right bears some investigation, but I would be nervous about application code having to keep that data consistent.

In reply to Re: database table advice by aufflick
in thread 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.