12

I've got an upcoming project and I can't decide whether to stick with SQL or switch over to NoSQL. It's basically a reporting system with the main interface being reporting on the data entered in by users.

Here's the schema I've got mapped out:

enter image description here

Because this schema is so nested, I started thinking about NoSQL. With SQL, I'm afraid I'm going to have a crap-ton of joins to get to the bottom of the tree (the Record model).

My concerns, though, are two-fold:

  1. I'm only just starting to get into NoSQL and I'm worried my knowledge may limit me because of the tight timeframe.
  2. Although creating data at the bottom of the tree will probably be relatively simple, I'm worried that it may be hard to report on without getting into some heavy map/reduce stuff (that I have zero experience with)

My question: Given my concerns, do you think this schema -- because of how deeply nested it is -- lends itself more to NoSQL? If so, do you think the reporting on the "records" will be difficult?

I realize that it may be difficult to answer these questions without more info, so please let me know what other info may be helpful in coming up with an answer.

Thanks in advance for your help!

Community
  • 1
  • 1
jmccartie
  • 4,956
  • 8
  • 50
  • 71
  • 2
    Questions related to this is asked on SO. Here is an example - http://stackoverflow.com/questions/3067866/why-exactly-do-we-use-nosql and http://stackoverflow.com/questions/2875432/use-cases-for-nosql. Hope that helps. Per me, you should be using relational database unless you have a valid reason to use no-sql considering the reasons it is to be used for. – shahkalpesh Oct 09 '11 at 18:57

3 Answers3

9

Just my opinion:

I Stared at diagram for approx 3 sec, this is clearly relational. Benefits of an RDBMS heavily outweigh a NoSQL solution here. Why would you want to use NoSQL? Are there 100,000+ records (may a million plus)? You need microsecond/millisecond performance?

NoSQL, as I understand, is not because you don't like lots of joins. It's because big systems for hierarchical data don't suit every situation. This suit this perfectly, however.

Kieren Johnstone
  • 41,277
  • 16
  • 94
  • 144
4

You can probably implode all of the {organisation, region,campus,event } hierarchy into one hierarchical / tree based / self-referential relation. Maybe "user", too. That would drastically reduce the number of tables needed. for an example, please take a look at this implementation: Interesting tree/hierarchical data structure problem (which is actually more complex than yours).

BTW: I don't have the faintest idea what "metric model" means. Inches? Miles to the gallon? Or just "measurements" ? Could you please explain a bit more what you intend to do?

EDIT: BTW2: the model you propose is technically not too difficult for postgres. But it is probably bigger than necessary for humans.

Community
  • 1
  • 1
wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • yes uhm . you don't always want to reduce the number of tables involved if that means heavy querying afterwards simply to determine which type of item we're looking for (i.e. list all regions gets very complicated when you do what you advise). – Morg. Jun 18 '13 at 14:22
2

My question: Given my concerns, do you think this schema -- because of how deeply nested it is -- lends itself more to NoSQL?

Deep nesting is not a point pro or contra SQL/NoSQL.

If so, do you > think the reporting on the "records" will be difficult?

That's the tipping point and here you don't give us the relevant information: What is this "reporting" thing in your case?

  • Does one report aggregate much data?
    E.g. does it simply aggregate all records and return a sum of them?

  • Does it aggregate over many of your layers?

  • Does a report evaluate strictly hierarchical or does it correlate event1.metric4.record42 to event2.metric18.record50 (or something like that)?

  • How much data must be transfered from the NoSQL DB to your application only to aggregate it an throw most of the parts away.

  • How unstructured is your data? Well - very structured it seems.

Those are typical situations/points where RDBMs have proven their value. If these items are not important in your case, then you can choose freely.

A.H.
  • 63,967
  • 15
  • 92
  • 126