Design for Category Feature

I am building a contact management system and I have a requirement for a contact category feature which allows a contact record to belong to multiple categories. For example a contact John Smith could belong to the categories Architects, Friends and Suppliers. The user is presented with a check list box and can check the categories that apply to each contact. The user needs to be able to rename the category names, or add categories of their own. This is the structure I am using:

Table PERSON

PERSON_ID INTEGER PRIMARY KEY
FIRST_NAME VARCHAR
SURNAME VARCHAR
etc...

Table CATEGORY

CATEGORY_ID INTEGER PRIMARY KEY
PERSON_ID INTEGER PRIMARY KEY
CATEGORY_NAME_ID INTEGER PRIMARY KEY Table CATEGORY_NAME

CATEGORY_NAME_ID INTEGER PRIMARY KEY
CATEGORY_NAME VARCHAR My question is whether this is the optimal design for this requirement? If I want to do a search for all contacts in several categories, I need to do a join between all 3 tables which sometimes impacts performance. The PERSON table may have tens of thousands of records, and as a result the CATEGORY table can have hundreds of thousands of records. Your help is much appreciated. Thank you.

Regards,

DB Received on Thu Oct 13 2005 - 03:15:55 CEST