2

I'm trying to design a database which has pyramidal relations. For example this is my data:

enter image description here

Well, how can I both store those numbers and keep their relationship?

Currently I do that like this:

// myTree
+----+------+--------+
| id | node | parent |
+----+------+--------+
| 1  | 8    | Null   |
| 2  | 10   | 8      |
| 3  | 3    | 8      |
| 4  | 14   | 10     |
| 5  | 6    | 3      |
| 7  | 1    | 3      |
| 8  | 13   | 14     |
| 9  | 7    | 6      |
| 10 | 4    | 6      |
+----+------+--------+

But in this case, I can only select one level of the graph by one query. While I need to select the whole branch for a node. Foe examle:

$node = 14;

Expected result:

[8, 10, 14, 13]

Note: Nodes are unique.

Anyway, how can I design the database to make it access-all-level by one query?

Community
  • 1
  • 1
Shafizadeh
  • 9,960
  • 12
  • 52
  • 89

3 Answers3

1

If you MUST do it in MySQL, @philipxy provided you with a good link, but you should know that MySQL is a poor choice for hierarchical/tree data.
Other DBMSs such as Oracle, SQL Server and PostgreSQL are some way better for the adjacency list approach since they are at least support iterative ("recursive") queries.
For a good fit, I suggest you'll take a look at neo4j

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88
1

You may want to look at nested sets. I knew about this technique for a long time before actually trying it on a working system, mainly because there is very little written about it that is positive. I was pleasantly surprised at the ease of querying graphical information that is extremely complicated using adjacency lists or any other method.

However, it has disadvantages that make it useful in a very specific environments: the list must be static. That is, once created, there is very little maintenance of the list itself -- movement of the nodes relative to each other, new nodes added or existing nodes deleted. This is because each row is dependent on other rows (which are dependent on other rows, and so forth). A change in the list could involve updating all the entries that follow it in the list. This is a lot of effort for a small change.

I had the perfect use case: the educational standards of all the States, District of Columbia and territories of the US. Educational standards change at most once a year and most states change them a lot less often than that. At any rate, once the school years starts, the standards that are in effect at the time remain in effect for the duration. So once the lists are build at the beginning of the year, they don't change until the start of the next year.

Imagine being able to query for information contained only in the subtree of any node of the tree. Absolute powah!

There were other good uses I never got around to implementing, such as school catalogs which also go thru very little changes once the semester starts.

By "changes" please note that I refer to changes that manipulate the structure of the tree: inserting a new node, moving a node from one place to another, removing a node, etc. Updates to the contents of a node are not a problem.

Also note that I had several weeks to play around to get familiar with the structure. You can write some radical queries, but it takes awhile to get the familiarity with the system to do so.

It may not suit your needs now, but it wouldn't hurt to get a basic understanding for when it may become useful. It's just too powerful a structure to ignore.

TommCatt
  • 5,498
  • 1
  • 13
  • 20
0

your database design is correct, only challenge is how from single query you get all child node. For that you can write a user defined function and call that function from query. Inside function can write a hierarchical query or database specific code, doesn't make much difference as this function will be called from query only.