Need help on structuring database for multiple "categories"

Hi all,

Can someone give me any clues as to how to structure my database /
tables? I am a bit clueless as to the best method, as this is the
first time I've dealt with nested categories.

I have a "Graphics" section on my website. This will be broken down
into categories (such as "Holidays" and "Automobiles") and each
category will have multiple themes (such as "Christmas", "Easter" and
"Honda", "Porsche"). So I take it I need three tables.

But I'm not sure what's the best way to do it. Normally if I have a
single category -> item relationship, I'd just do:

category
- id
- name

item
- id
- cat_id
- name

But now I'm dealing with a category and multiple themes as well.

However, aside from needing help on the actual structure of the
database, I would like it so that if one were to visit "/holidays",
they would get *all* images associated with the themes that belong to
the "holidays" category, such as "Christmas" and "Easter". Could
someone tell me if it's possible to do this in a single SQL call, or
would I need to do multiple ones? My knowledge is very basic outside
of your standard SELECT statements :)

Thanks for any help,
Rob

>Can someone give me any clues as to how to structure my database /
>tables? I am a bit clueless as to the best method, as this is the
>first time I've dealt with nested categories.
>
>I have a "Graphics" section on my website. This will be broken down
>into categories (such as "Holidays" and "Automobiles") and each
>category will have multiple themes (such as "Christmas", "Easter" and
>"Honda", "Porsche"). So I take it I need three tables.

Given only the description above, I'd assume that you need one table
for images, with an entry for each image containing category (or
category ID), theme (or theme ID), and image (or reference to it).

How do themes and categories relate? Do you, for example, have a
category "Holidays" with theme "Honda", along with the category
"Automobiles" and themes "Honda" and "Easter"?

>But I'm not sure what's the best way to do it. Normally if I have a
>single category -> item relationship, I'd just do:
>
>category
>- id
>- name
>
>item
>- id
>- cat_id
>- name
>
>But now I'm dealing with a category and multiple themes as well.

If a theme can be treated as a "subcategory", you might have:

theme
- theme_id
- cat_id
- name

and add theme_id to the item.

>
>However, aside from needing help on the actual structure of the
>database, I would like it so that if one were to visit "/holidays",
>they would get *all* images associated with the themes that belong to
>the "holidays" category, such as "Christmas" and "Easter". Could
>someone tell me if it's possible to do this in a single SQL call, or
>would I need to do multiple ones? My knowledge is very basic outside
>of your standard SELECT statements :)

If an item record contains a category and a theme id, you can SELECT
on just the category, just the theme, or both.