Struggling with a data modeling problem
- by rpat
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.
