Menu Model

[eluser]jedd[/eluser]
[quote author="Think Floyd" date="1236884865"]
DB structure:

TABLE: category
id
title

TABLE : subcategory
id
title

JOIN TABLE: categories_subcategories
category_id
subcategory_id
[/quote]

May I suggest that life would be simpler, and this is true IFF you are sticking with two levels here, to have two tables instead. Roughly like:

Code:

CREATE TABLE category
     id          SERIAL,
     title       VARCHAR

CREATE TABLE subcategory
      id         SERIAL
      cat_id     BIGINT    // FK to category.id of course
      title      VARCHAR

Your current schema only allows two levels deep anyway because of the limitations of the 'join' table.

If you wanted to get excited you could have a single table that had a parent ID column (0 denotes category, etc). This is pretty standard stuff, though.

Point being, your selects would be easier, and consequently your code would be easier (read: your problem would be easier).