0

I have an Excel of raw panel data (cross sectional + time series) sent to me by customers (I cannot change this). For those not familiar, imagine the gdp time series for each country. I need to transfer this data into a database and I am about to choose the best technology, either a SQL (Microsoft SQL Server), or No SQL (MongoDB).

In principle I am quite sure you can use both, but probably an SQL-like is less suitable. My comments:

  • SQL: I would need to replicate the name of the country as many times as the timestamp avalilable for its gdp, and stacking each country one below the other. When I need to update (because every 3-months a new gdp value comes out), I would need to code complex queries in order to correctly insert the new value where a country ends but before the next one starts (FYI: to insert data into the db I will have a python code which push new data into the db);

  • NoSQL: here I can have a collection named GDP, and documents related to each country, so for the document "UK", I'd have a key called (time-series) and a value like [[1-1-2022, 2.3], [1-4-2022, 3.1], ..] (list of list where the first is the timestampt and the second value the gdp) and so on;

in addition since this data are quite messy it can happen that from time to time some data is missing so a NoSQL simply will not have a value, which is less problematic than a SQL. To me, NoSQL is better in this case.

Since in this forum there are expert people (I am not..), I would like to know if my reasoning is right, and for panel data a NoSQL (es. MongoDB, because is free..) type is more suitable.

Improve this question
7
  • 1
    A relational database has no concept of inherent order in the data. The idea of "insert the new value where a country ends but before the next one starts" is not relevant. If you're trying to manage the values of a surrogate integer ID, don't.
    – Michael Green
    Commented Apr 19, 2022 at 10:12
  • 2
    If anything, Mongo would be more work. It uses arrays, and they are positional, so you'd have to ensure they're re-sequenced when data changes.
    – Michael Green
    Commented Apr 19, 2022 at 10:14
  • yes..there is no order that's true, I had in my mind but explained it badly..I thought of first make a select with a group by country and then using order by timestamp to obtain what I meant above..but yeah in principle I can just throw anything in it..it will set everything randomly and then reorder by querying
    – Dark2018
    Commented Apr 19, 2022 at 12:53
  • about the "more work", I cannot really get it..I mean everytime I have to put a new value I need for every document to access only the key time series and add a new list [timestamp, new value], while in an SQL solution I would use an insert query..now, apart for the speed, I do not see the reason for having more work in a NoSQL.. but maybe I misunderstood your point
    – Dark2018
    Commented Apr 19, 2022 at 12:58
  • I was extrapolating from your "insert..where ends" statement and inferring a lot, likely more than you intended, about maintaining strict physical ordering of values, with changing and late-arriving values.
    – Michael Green
    Commented Apr 19, 2022 at 22:23

1 Answer 1

Reset to default
0

I agree with the comments that for the use cases you described, it sounds like you'll likely end up needing to do at least the same amount of work (if not more) manipulating and maintaining the data in a NoSQL solution as you would in a relational database management system.

The advantage of a NoSQL solution is usually for the use case where you have a very loosely defined / highly changing schema, and the amount of work to maintain those changes in the database layer outweighs the benefits of maintaining them there. A good use case is especially if you don't need to do any transformations / manipulations to the data, and you just want to house it exactly as it comes in, leaving the work to some other consumer to handle structuring it, such as end users whom are self sufficient and are reporting directly off of it (e.g. a PowerBI scenario perhaps).

For most people this isn't the case, rather they usually work directly or close with the consumer that needs to maintain some sort of defined structure and / or they want to do additional transformations/ manipulations to the data in the database layer, such as your case sounds.

So for you, it really comes down to which system you feel more comfortable maintaining, as both will handle your goals equally and both will require some level of work to maintain. Objectively, they're pretty equal systems otherwise.

For further reading on the benefits and drawbacks of one system over the other, please see this answer I wrote on a somewhat relevant DBA.StackExchange question.

Improve this answer
1
  • 1
    yes maybe for my specific case they sound quite equivalent, thanks for the link to your answer
    – Dark2018
    Commented Apr 19, 2022 at 13:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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