2

I have the following information that should be retrieved by using several dependent select fields on a web form:

Users will be able to add new categories.

Food
 - Fruits
   - Tropical
      - Pineapples
          - Pineapples - Brazil
          - Pineapples - Hawaii
      - Coconuts
   - Continental
      - Orange
 - Fish

....

This data should come from a database.

I realize that creating a table for each category here presented is not a good schema perhaps, so I would to ask, if is there any standard way to deal with this?

I'm also aware of this schema example: Managing Hierarchical Data in MySQL

Is there any other (perhaps more intuitive way) to store this type of information ?

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
MEM
  • 30,529
  • 42
  • 121
  • 191
  • How much easier than a parent_id shall it be? – Kawu May 18 '11 at 22:11
  • In my view, in a perfect world, we'd all be using XML databases, which can store relational data or hierarchical data in a more natural form. – Brett Zamir May 19 '11 at 10:59
  • 1
    http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database – orangepips May 19 '11 at 16:19

3 Answers3

2

The link you provided describes the two standard ways for storing this type of information:

  • Adjacency List
  • Nested Sets
Brian Fisher
  • 23,519
  • 15
  • 78
  • 82
  • Thanks. So those are the ways to go hm... ? – MEM May 18 '11 at 22:10
  • Yeah. I generally use nested sets for larger sets of hierarchical data and Adjacency Lists for smaller sets. The article you linked to describes the advantages of the different approaches. – Brian Fisher May 18 '11 at 22:14
  • @MEM - your question says users will be able to add categories. I think that ought to rule out Nested Sets, as the mechanism for shuffling the LFT and RGT values (as shown in the article) is quite quite horrible. BTW, handling hierarchical data in pure SQL is one area where Oracle has lots to offer. See this answer for an overview: http://stackoverflow.com/questions/5547349/rails-model-with-foreign-key-to-itself/5547450#5547450 – APC May 19 '11 at 04:28
2

One issue your question didn't raise is whether all fruits have the same attributes or not.

If all fruits have the same attributes, then the answer that tells you to look at the link you provided and read about adjacency lists and nested sets is correct.

If new fruits can have new attributes, then a user that can add a new fruit can also add a new attribute. This can turn into a mess, real easily. If two users invent the same attribute, but give it a different name, that might be a problem. If two users invent different attributes, but give them the same name, that's another problem.

You might just as well say that, conceptually, each user has their own database, and no meaningful queries can be made that combine data from different users. Problem is, the mission of the database almost always includes, sooner or later, bringing together all the data from the different users.

That's where you face a nearly impossible data management issue.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
  • Indeed. The all hard part here is exactly that. I will need to cross information, and compare... I have no clue how to achieve that here. – MEM May 19 '11 at 11:37
1

Kawu gave you the answer.... a recursive relation (the table will be be related to itself) aka Pig's Ear relation.

You example shows a parent with several children, but you didn't say if an item can belong to more that one parent. Can an orange be in 'Tropical' and in 'Citrus'?

Each row has an id and a parent_id with the parent_id pointing to the id of another row.

id=1 name='Fruits' parent_id=0
id=2 name='Citrus' parent_id=1
id=3 name='Bitter Lemon' parent_id=2
id=4 name='Pink Grapefruit' parent_id=2

Here are some examples of schemas using this type of relation to provide unlimited parent-child relations:

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
user212102
  • 91
  • 3
  • perhaps that will arrive.Let's say user A as a product Orange A, and user B as a product Orange B. One will store their orange inside "Citrus", the other will be inside "Tropical" - I don't mind, since both make sense. So, we will allow our users to find that product (orange), either by navigating to Citrus or Tropical, if they go to **Citrus** they will find **USER A orange**, if they go trough **Tropical** they will find **USER B Orange** but they will find and orange. It's not perfect... but if the point is to find and store an orange, even if redundancy, I can leave with that. – MEM May 19 '11 at 11:46
  • Then the above would work... but it still does not address the case where User A explicitly wants to store Orange A under more than one category. – user212102 May 21 '11 at 19:49