Search code examples
mysqlsqldatabase

The best way of storing many to many hierarchical data in sql

I have search the internet of a way to store many to many hierarchical data in SQL, but SQL doesn't seems to be very optimized for that type of data. I want my data to be stored this way because I need it to be flexible. I don't know if I am storing 1 or 100 fields hierarchically.

Requirements:

  • The data also needs to be searchable.
  • Reading the data need to be fast.

This is the solution I have found: http://en.wikipedia.org/wiki/Nested_set_model but it will make my table very large and all fields will be in text even when I store a number or Boolean.

  • Are there alternatives to this model?
  • Do I have to go for a No-sql database?
  • Is there any trends on which database model I should use?
  • Which solutions are being used in companies that need this sort of storage?

Looking forward hearing from you.

Solution

  • sql dosent seems to be verry optimised for that type of data.

    And there you gave yourself the answer: The best way would be to go to a DBS which is optimized for graphs.

    But if for some reason you insist to stay in relation land, there are some techniques to store graph structures.