CODEWITHSUNDEEP
mysqldatabase-designhierarchical-datadatabase-schema
MEM
MEM

Reputation: 31397

How to store hierarchical information into a database?

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 ?

Upvotes: 2

Views: 1551

Answers (3)

user212102
user212102

Reputation: 81

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:

Upvotes: 1

Walter Mitty
Walter Mitty

Reputation: 18950

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.

Upvotes: 2

Brian Fisher
Brian Fisher

Reputation: 24009

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

  • Adjacency List
  • Nested Sets

Upvotes: 2

Related Questions