SQL doesn't naturally lend itself to nested data. However, there are several clever solutions to managing tree-like data in SQL, but most of them involve one of the following:

• adding columns to your tables that explicitly map the current nesting (and triggers to keep them sync'd)
• a huge brain (google for "sql nested sets")

If you use PostgreSQL, you can avoid both of these drawbacks (well, a huge brain isn't necessarily a drawback if you have one available - unfortunately for me, most of the huge brains I know belong to evil robots). Actually, any database with a decent procedural language will work, but I only know of one in the open source world: PostgreSQL.

Let's assume we have a table that was created like this:

CREATE TABLE accounts (
  id SERIAL PRIMARY KEY NOT NULL,
  accounts_id FOREIGN KEY (accounts),
  account_name TEXT
);

And it's got records like this:

    account_name     | id  | accounts_id
---------------------+-----+-------------
 account             |  78 |
 sub account         | 102 |          78
 sub sub account     | 151 |         102

As you can see, this table represents a tree graph, where an account can have one or more subaccounts.

This is a really simple and easy-to-maintain system. Unfortunately, it's not so easy to ask questions like "given an account, what are all the subaccounts of that account?" Obviously if there's only one level of subaccounts, the answer is easy, but if you have multiple levels, then it isn't quite so simple.

Here's a fairly simple PL/PGSQL function that can answer that particular question for you. Given the id of an account, it returns a list of rows informing you of all the children of that account.

CREATE FUNCTION get_subaccounts ( parent_id INTEGER )
RETURNS SETOF accounts AS $$
  DECLARE
    child accounts;
    schild accounts;

  BEGIN
    FOR child IN SELECT * FROM accounts WHERE accounts_id=parent_id LOOP
      RETURN NEXT child;
      FOR schild IN SELECT * FROM get_subaccounts ( child.id ) LOOP
        RETURN NEXT schild;
      END LOOP;
    END LOOP;
    RETURN;
  END;
$$ LANGUAGE 'plpgsql';

You can now do a query like this:

db=# select id, accounts_id from get_subaccounts ( 78 );
 id  | accounts_id
-----+-------------
 102 |          78
 151 |         102
(2 rows)