Databases
StackOverflow2013
Postsdbo
POStruggling with a data modeling problem
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POStruggling with a data modeling problem
    primarykey
    data
    text
    <p>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.</p> <p>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</p> <p>Food Services > Restaurants > Italian</p> <p>However, an organization may belong to multiple groups. A restaurant may also serve Chinese and Italian. So it will fit into 2 classifications</p> <p>Food Services > Restaurants > Italian<br> Food Services > Restaurants > Chinese</p> <p>The classification reference tables would be like the following:</p> <p><strong>ORG_CLASS (RowId, ClassCode, ClassName)</strong></p> <pre><code>1, FOOD, Food Services </code></pre> <p><strong>ORG_CATEGORY(RowId, ClassCode, CategoryCode, CategoryName)</strong></p> <pre><code>1, FOOD, REST, Restaurants </code></pre> <p><strong>ORG_TYPE (RowId, ClassCode, CategoryCode, TypeCode, TypeName)</strong></p> <pre><code>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 </code></pre> <p>The actual data tables would be like the following:</p> <p>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 </p> <p><strong>ORGANIZATION_TABLE (OrgGroupId1, OrgGroupId2, OrgGroupId3, OrgName, OrgAddres)</strong></p> <pre><code>100,103,NULL,MyRestaurant1, MyAddr1 100,102,NULL,MyRestaurant2, MyAddr2 100,104,105, MyRestaurant3, MyAddr3 </code></pre> <p>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.</p> <p>During Search, If all three classification are chosen, It will be more specific. For example, if </p> <p>Food Services > Restaurants > Italian is the criteria, the where clause would be <code>'where OrgGroupId1 = 100'</code></p> <p>If only 2 levels are chosen</p> <p>Food Services > Restaurants</p> <p>I have to do <code>'where OrgGroupId1 in (100,101,102,103,104,105, .....)'</code> - There could be a hundred in that list</p> <p>I will disallow class level search. That is I will force selection of a class and category</p> <p>The Ids would be integers. I am trying to see performance issues and other issues.</p> <p>Overall, would this work? or I need to throw this out and start from scratch.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USrpat
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COthis might be overkill for your purposes, but a good article about managing hierachical Data: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html
      singulars
      1. POStruggling with a data modeling problem
      1. UStrickwallett
    2. COThanks for the link. It indeed is a good reference. It did let me have another option for the classification. This approach in the article is more flexible and does not limit the number levels of classification.
      singulars
      1. POStruggling with a data modeling problem
      1. USrpat
    3. CO@trickwallet: The article is not available anymore at this address. It is now here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
      singulars
      1. POStruggling with a data modeling problem
      1. USPaŭlo Ebermann
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload