Mysql & other databases - Organizing Categories

I have a bit of a puzzle I'm trying to figure out....I was wondering if anyone might be able to help me sort it out....or in the direction.

I have 11 main categories and about 185 sub categories that may appear in one or more main category.

In the sub categories are companies.

The user will enter the site and search by postal code. I want to only show the categories & sub categories that have a company with that postal code.

I'm just having trouble figuring out how i should set up my databases.

Any guidance or help would be greatly appreciated... I hope this made some sense.

Thanks.

OK Here's the basics tables I would create.

tblCategory with a primary key and the category info.
tblSubCategory with a primary key and the category info.
tblCompany with a primary key and the company info.
tblAssocCat_SubCat with a primary key and two fields for the Category and SubCategoy keys
tblAssocSubCat_Company with a primary key and two fields for the SubCategory and Company keys

then build a stored query to give you the zips for a SubCategory:

SELECT Distinct SubCatID, ZipCode
FROM tblAssocSubCat_Company INNER JOIN tblCompany on tblAssocSubCat_Company.companyID = Company.companyID

That should get you started.

Thanks for the help

ok i have the category table with my 11 categories and my sub category as well as the company table


How do you get the keys from those into those other tables...i'm a little confused

There are a couple of different ways to do it. It depends on what raw data you have.

1. you can create a form to manually assign the sub Categories to the categories and the companies to the categories.
2. if you have an excel spreadsheet with the info , or can easily get the info into an excel spreadsheet, you can use it to do the association.

Which you do depends on how the Categor, Sub Category, and Company data will be maintained.

If you will be keeping it all in the DB then option 1 is your best choice, otherwise option 2