database design mysql php foreign keys

I am creating my very first database dynamic driven web page. I am sensitive, please be kind.

I want to create a resource section for my bike trails web site.

Resources will have categories such as bike clubs, state parks, bike shops etc.

Each resource "subject will have name, description, url, and state it is located in.

I would like users to be able to choose a category and the state and then display the results of the chosen category for the chosen state.

I have thought and thought and this is what I have come up with for database design


Resource Catgeories Table
resource_cat_ID (primary key, auto inc)
resource_cat_name


Bike Club Table
bike_club_ID (primary key, auto inc)
resource_cat_ID (foreign key)
state_ID (foreign key)
bike_club_name
bike_club_url
bike_club_description

State Table
state_ID (primary key, auto inc)
state_name

I would create a table for each resource "subject" as per Bike Club Table?

Is there a more optimized way to do this?

and how would the query be?

This article offers a step by step approach to transform a UML object-oriented class model in a purely relational database.

http://www.methodsandtools.com/archive/archive.php?id=9

You're almost there.

Simply rename the bike club table to resources and
bike_club_name, bike_club_url and description to resource_name, resource_url, resource_description respectively.

Then the query would be:

SELECT resource_name, resource_url, resource_description
FROM resources
WHERE (value from resource_cat_ID field) = resources.cat_id AND (value from state_ID field) = resources.state_id


You don't have to create a table for each category, you allready store them in Resource Catgeories Table.

Simple as that!!

Thank-you for your replies.
The article martinig posted was very helpful. I am still trying to wrap my head around the relationships. Heady stuff for someone as directionally challenged as me.

Markos suggestion of putting all the resources in one table also set me on track.

This is what I did.
I revised the tables as follows:

resource table
res_id (pk, auto_inc
res_name
res_url
res_desc

resource categories table
res_cat_id (pk, auto_inc)
res_cat_name

states table
states_id (pk, auto_inc)
states_name

I added a new "join table"
res_rescat_states table

res_id
res_cat_id
states_id

and this worked fine for me.
Is it overkill?

Now to complicate matters I need to add regions.

Each state has several regions. Do I make a regions table? This is confusing because some states have regions that are named the same as others. Do I put a state in it as FK? but I already have states in the "join" table.

How can I do it so if I want to query a region I will get the resource info and state associated?