how to save tree data on database to analyze


Problem :

I'm going to make web application with d3js. It will have tree map about something and able to add/delete by user. I wonder that how to save tree data on database. data could be

var treeData = [
  {
    "name": "Top Level",
    "parent": "null",
    "children": [
      {
        "name": "Level 2: A",
        "parent": "Top Level",
        "children": [
          {
            "name": "Son of A",
            "parent": "Level 2: A"
          },
          {
            "name": "Daughter of A",
            "parent": "Level 2: A"
          }
        ]
      },
      {
        "name": "Level 2: B",
        "parent": "Top Level"
      }
    ]
  }
];

or

source,target
A1,A2
A2,A3
A2,A4

actually it doesn't matter which structure I use because the data can save itself on my mysql database as "TEXT". But I want to analyze tree data on database such as

select * from treemap where secondChildren = 'stackoverflow';
select * from treemap where root = 'c++' and lastChildren = 'java';

since my treemap will be dynamically made by user, I cannot make fields as much as children nodes. any idea do you guys have? :)



Solution :

As Neo M Hacker said, neo4j is great for this sort of thing.

You tagged with mySQL, though, so if you're stuck with an relational database, the best solution I've seen is to have an "ancestors" column which stores a delineated list of IDs of ancestors up the tree. That way you can find whole subtrees or ancestor lists at once. In ruby the ancestry gem is a great example of how to go about this:

https://github.com/stefankroes/ancestry


Mysql Tutorials

Mysql Howto..

How to count and limit record in a single query in MYSQL?

How to atomically move rows from one table to another?

How to restore mysql from backup?

How do I account for missing xPaths and keep my data uniform when scraping a website using DOMXPath query method?

How to start from square one with 0xdbe [closed]

How to select a single row a 100 million x

How to check if entry contains word and print random entry from table PHP, MySQL

MySQL: How to find newest row using 3 keys? - not as simple as it sounds

How to optimize MySQL UPDATE

How to compare two DB tables of different number of rows based on one attribute in MySql?

How to update a table using trigger every 30 days?

How to generate unique id for pair strings on certain conditions in MySQL

How to omit checking for SqlNullValueException in Mysql Connector/NET

How to insert multiple rows at once in MySQL DB using Struts2?

how do you know how to design a mysql database when creating an advanced php application?

How “perfect” does a database have to be?

How do I connect to a database I created in HostGator?

How could I tie Rails user authentication/authorization to existing permissions within a MySQL database server?

How to avoid encoding warning when inserting binary data into a blob column in MySQL using Python 2.7 and MySQLdb

how to save selected value from drop down list to mysql in php

How to connect to a MySQL database on another domain?

codeigniter: how to insert multiple array value into mysql database in one time?

How to track source files loaded in MySQL?

MySQL How to select date field exactly 7 days before today and for time last 1 hour

How do I escape reserved words used as column names? MySQL/Create Table

Mysql, How to group a set of rows by using the max value of another column?

Unsure how to approach json php mysql integration

How can I make sure PHP json_encode will encode whatever data I read from the db?

How to insert date to MySQL from html input?

how to select recently inserted data into mysql database?