Database design question....

I am building an application which is part directory (yellow page style). As I began to build the database and write up the design I found my self challenged with the following:

In part of the db I have a table for companies, categories and subcategories. The problem is that some of the companies fall into more than 1 category and I am not sure how to effeciently design this.

Can anyone share some thoughts or suggestions?::The problem is that some of the companies fall into more than 1 category and I am not
::sure how to effeciently design this.

What you need to make is a m:n relation and is a standard pattern for handling this standard problem in db design.

First, you should have three tables, but NOT two for category and subcategory.

You should have one table for Category (and a subcategory IS a category, build a tree using a relation pointing back to the table), one for the entires (companies), one for coupling one entry with one caategory - a mid table that allows you to assign an entry to x categories.

This table would basically contain one field (fk-relation) to the category, one to the entry.

Get a beginner book for SQL and relational databases that explains M:N relations in depth.|||doesn't look that hard, basic relational db design:

Category
* CatID (PK)
* CatName
* ...

Company
* CompID (PK)
* CompName
* ...

Company_Category
* CompID (PK ; FK to Company.CompID)
* CatID (PK ; FK Category.CatID)

By the way, implement subcategories as a category with a parent. Category table becomes:

Category
* CatID
* CatName
* CatParentCatID (maybe NULL for top level category)|||look up tables. Just store the primary keys in the look up tables.