I am struggling with a data model (I use MySQL for the database). I am uneasy about what I have come up with. If someone could suggest a better approach, or point me to some reference matter I would appreciate it.

The data would have organizations of many types. I am trying to do a 3 level classification (Class, Category, Type). Say if I have 'Italian Restaurant', it will have the following classification

Food Services > Restaurants > Italian

However, an organization may belong to multiple groups. A restaurant may also serve Chinese and Italian. So it will fit into 2 classifications

Food Services > Restaurants > Italian
Food Services > Restaurants > Chinese

The classification reference tables would be like the following:

ORG_CLASS (RowId, ClassCode, ClassName)

1, FOOD, Food Services

ORG_CATEGORY(RowId, ClassCode, CategoryCode, CategoryName)

1, FOOD, REST, Restaurants

ORG_TYPE (RowId, ClassCode, CategoryCode, TypeCode, TypeName)

100, FOOD, REST, ITAL, Italian
101, FOOD, REST, CHIN, Chinese
102, FOOD, REST, SPAN, Spanish
103, FOOD, REST, MEXI, Mexican
104, FOOD, REST, FREN, French
105, FOOD, REST, MIDL, Middle Eastern

The actual data tables would be like the following:

I will allow an organization a max of 3 classifications. I will have 3 GroupIds each pointing to a row in ORG_TYPE. So I have my ORGANIZATION_TABLE

ORGANIZATION_TABLE (OrgGroupId1, OrgGroupId2, OrgGroupId3, OrgName, OrgAddres)

100,103,NULL,MyRestaurant1, MyAddr1
100,102,NULL,MyRestaurant2, MyAddr2
100,104,105, MyRestaurant3, MyAddr3

During data add, a dialog could let the user choose the clssa, category, type and the corresponding GroupId could be populated with the rowid from the ORG_TYPE table.

During Search, If all three classification are chosen, It will be more specific. For example, if

Food Services > Restaurants > Italian is the criteria, the where clause would be 'where OrgGroupId1 = 100'

If only 2 levels are chosen

Food Services > Restaurants

I have to do 'where OrgGroupId1 in (100,101,102,103,104,105, .....)' - There could be a hundred in that list

I will disallow class level search. That is I will force selection of a class and category

The Ids would be integers. I am trying to see performance issues and other issues.

Overall, would this work? or I need to throw this out and start from scratch.