Struggling with a data modeling problem

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 开发者_JAVA百科trying to see performance issues and other issues.

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

---

I don't like the having three columns for the "up to three" classifications. In my opinion it would be better to have a cross-reference table that allows your many-to-many mapping between organisation and type, i.e. table ORGANISATION_GROUPS with columns OrganisationId, OrgGroupId.

To sort out the problem of being able to query a different levels of classification specified you could setup this cross-ref table to hold the actual classifications, i.e. ORGANISATION_GROUPS instead has columnns: OrganisationId, ClassCode, CategoryCode, TypeCode.

This will make queries at different levels of classification very easy.

For referential integrity to work with this scheme I'd then suggest not using surrogate integer keys for your ORG_* tables but instead setting the primary key to be the real unique key, i.e. ClassCode, CategoryCode, TypeCode for ORG_TYPE.

---

The problem i see in your design is that it is a bit rigid. A more flexible approach you might want to consider is following:

First you would have a table for classes, categories, types and any other classification type. This table would be auto-referenced. All registers would have a field referring to its immediate parent, like following:

CLASSIFICATION (Id, Description, Parent_Id)

ITAL, Italian, REST
CHIN, Chinese, REST
MEXI, Mexican, REST
REST, Restaurant, FOOD

Next you would have, as @John pickup suggested, an intermediate cross-reference table between your restaurant (or whatever you need) table and the classification table which would contain only a composite primary key, being its components the primary key of both tables.

FOODSERVICE_CLASSIFICATION (Rest_Id, Class_Id)

100, ITAL
100, CHIN
101, MEXI
102, CHIN

It would be advisable to limit it so that only leaf registers of the CLASSIFICATION table can be referenced in the cross-reference table.

Your example of looking for all restaurants would be as simple as looking for all child categories of REST and search for them in the cross-reference table. This can be written in a single select in Oracle (not sure about other RDBMS).

This way you can:

• have multiple categorization for your restaurants without being limited to 3 categories.
• Do quick searches using the cross-reference table.

Mind you, this schema would work supposing your categorization is like a tree with a base category acting as the root. If instead you need a more loose categorization you would probably need a tags approach.

Btw, I also agree with @John Pickup that it is better to use real primary keys in this case.

HTH