1

I have a question regarding Hierarchical data.

Our app shows posts, that are structured inside parents. Here's how it goes:

prof->sprof->subject->post

(Each prof can contain multiple sprofs, and each sprof can contain multiple subjects, and each subject contains multiple posts)

Right now, we've set up a temporary MySQL Database that looks like this:

There are 4 tables: profs, sprofs, subjects and posts. Inside the table profs you have a row for each prof - and each prof has it's unique ID.

Inside the table sprofs, each sprof has it's own unique ID, but It also has a field for the ID of its parent. For example, if there's a prof with the ID 1, a sprof might have a parent_id of 1 (to indicate that its prof (its parent) is prof number 1).

And so on - each subject has a field for its sprof's ID, and each post has a field for its subject's ID.

So for example, if we want to get all the posts under the subject number 2, we do:

SELECT * FROM 'posts' WHERE 'subject_id'=2

A friend told me, that MongoDB can have structured data, so for example I could put a "table" of "posts" under a "row" inside the "table" of "subjects".

Is it true? Will it help? Will it be faster than using MySQL?

I'm not familliar with MongoDB at all, but I'd like to learn that anyway.

0

The question to answer first is, are any of the possible children bounded or unbounded sets of data? The next question is, how are you going to be requesting the data? Another question would be, how often will the data be written?

One of the things you'll learn quickly with MongoDB is the answer to any schema questions is almost always "It depends". It depends on the answers to the questions I asked above and a few more. To get you on your way though, MongoDB has some great documentation on the subject of hierarchies. This gets you part of the way to your solution.

Then you have to understand your data access patterns and when to normalize or denormalize data. Something to help you make that decision can be read in this great blog.

Sorry to leave you with a not so cut and dry answer. Yes, MongoDB can take care of hierarchical data. How should it be done? It depends. Will it be faster than MySQL, it depends. Can it be faster than MySQL? Most definitely.

Scott

  • This doesn't really give an answer. – jwg Oct 12 '15 at 16:29
-1

That's true. You can put hierarchical data in MongoDB. But I think you could perfectly fit what you got in one MySQL table?

Let's say structure would look like

ID  ParentID    TypeID  Value
1   NULL        1       'Dr. Mittens'
2   1           2       'Doc. Woofer'
3   2           3       'Name of Subject'
4   3           4       'Post 1'
5   3           4       'Post 2'
6   2           3       'Name of another Subject'

I might be wrong - correct me if you can.

  • 1
    I wouldn't create such a structure in MySQL because it still lacks recursive queries. So querying that will be really difficult. – a_horse_with_no_name Jan 11 '15 at 12:03
  • Good point, I'm working on SQL Server, not MySQL. Recursive queries work there well. But in the end it depends how user wants to query data. Having 4 different tables might be better approach. – Evaldas Buinauskas Jan 11 '15 at 12:15
  • Hey, thanks - I'm not sure how should I use recursive queries, but your solution doesn't seem to fit exactly hat we need, because I think in the future we'll have fields that don't apply for all types of rows, e.g. 'icon_url' would only be for profs but not sprofs,subjects or posts. Also your solution will make a very big table, which would make searching through it slower (correct me if I'm wrong, I'm pretty new to this) – Reuven Karasik Jan 11 '15 at 12:31
  • Its a long discussion if you need to use Mongo here. Mongo is good for hierarchical data. What is going to be the size of your dataset? Do you perform complex join with your tables? Your database is mainly read or write heavy? – Antonios Jan 11 '15 at 22:40
  • I would not use this design in a SQL database, regardless of which one or whether it supports recursive queries. – jwg Oct 12 '15 at 16:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.