NoSQL isn't a very well defined term and all the solutions that run under this name have very different features, so a lot may be possible or not depending on what exactly you are planning to do with it.
Basically you could use some of the more general solutions like maybe MongoDB or Cassandra to simply replace your current relational database. In some cases this makes more sense in others less, but it will work once your team got used to it. Certain things will be easier then, others will be more difficult and you must weight those options against each other and decide (which often enough will mean that there are no advantages big enough and the simple fact that everybody in the team feels most comfortable with relationals and SQL will make the decision easy)
Other NoSQL solutions that are more specialised are not really good candidates to replace your relational DB, like graph databases or simple key value stores. So lets from here talk mainly about those databases that are at least to some degree similar to relational databases.
Scenario 1
Where I work we have exactly this scenario, though quite more complex with a lot of different attributes per article. Some of those attributes in hierarchies like Apple -> iPad -> Air.
The data is still stored in a relational database. But: searching this in real time became a pain. With SQL it was slow and code would have been terribly complex. Selects over many tables, with the additional option to exclude certain attributes like "not blue".
In this case Apache Solr or Elastic Search are a solution. Though of course data is duplicated from the relational database.
But from here our experience with this kind of document store showed that it can handle certain problems very well and we will consider to replace part of the existing relational structure with some other kind of storage. So not the whole database where we also store all the transactional data like orders etc, but for example take out all the attribute information which can be handled much better in the aggregate like data structures of NoSQL.
Scenario 2
Difficult to say, since what you describe is most likely only a very small part of your user handling. Having schemaless storage is an advantage with many NoSQL databases. But some relational databases allow to store such data too (as long as you don't need to query it via SQL in most cases).
Cassandra for example would allow you to define column families in such a case, where your first set of attributes would be one such family and the variable attributes another one.
As somebody said: NoSQL is less about storage and more about querying. So the question is what will be the typical use case for those queries.
A typical problem would be the transactional data here. If you want to store orders, one way would be a schema where users and their orders form an aggregate (kind of user document that contains the orders as subdocuments). This would make getting a user together with his orders very simple and fast, but would make it very difficult to retrieve all orders from last month for sales statistics.
Also strengths of NoSQL solutions are that it can be easier to run them on multiple clusters if you have to work with very large datasets.
Conclusion: Both your scenarios could be modelled with certain NoSQL solutions, but I don't think that (assuming they have to run in a larger environment) they really justify a large extra effort in learning, training and implementation and maybe some other additional disadvantages because both are not specific enough to really leverage the strengths of NoSQL. At least not in that simple form you describe it. Things may become very different once some aspects you describe would be very, very prominent in your usage scenario, like in scenario one the attribute data becomes very complex or in scenario two the variable fields become the largest part of data you store with every user.
This is tricky, because you've modeled your bookings as time intervals with granularity as fine as your DB allows. Perfectly natural to do, but as you've found out it makes some comparisons difficult.
Max of
For each booking that overlaps given timeRange
return sum of
each booking that overlaps this booking and given timeRange
The problem with this algorithm is that it checks that each other booking interval matches the one currently being examined (the foreach iteration), but it doesn't check overlapping bookings against each other, to see if they line up. A run through of your second example goes like this:
- Select 7x booking
- 7x does not overlap with 2x; +0
- 7x overlaps with 10x; +10
- Total 17
- Select 2x booking
- 2x does not overlap with 7x; +0
- 2x overlaps with 10x; +10
- Total 12
- Select 10x booking
- 10x overlaps with 7x; +7
- 10x overlaps with 2x; +2
- [Missing step: check if 7x and 2x overlap]
- Total 19
- Max 19
Is it reasonably possible to map your data to nice, neat discrete chunks of some size? For example, do your bookings generally begin and end on the 15s (12:00, 12:15, 12:30, 12:45)? If so, you can change your algorithm to compare bookings against a static time interval, rather than each other, and drastically reduce the required number of comparisons:
Max of
For each 15 minute chunk in timeRange
Sum quantities of all bookings overlapping this chunk
In terms of SQL implementation, choose an interval size and use a numbers or tally table to generate an inline query to create the chunks:
select @startTime + interval (15 * numbers.value) minute as start
, @startTime + interval (15 * (numbers.value + 1)) minute as end
from numbers
where (@startTime + interval (15 * numbers.value) minute) < @endTime
(Off the cuff, may contain minor syntax or math errors)
This is a relatively sane way of performing this query in SQL without recursion. It has the obvious drawback that it will never perfectly align with your current schema, but do you really need absolute perfection?
I've used 15 minutes as an example size. You can easily make this as finely grained as you like: 5 minutes, 1 minute, 1 second, etc. There must be some point at which the granularity is too fine, because MySQL's timestamp type does not possess arbitrary precision. "Booking" to me implies something involving humans actually showing up. If this is true, I can't imagine that an interval size smaller than one minute would be appropriate.
In the comments you expressed some concern about performance because of a large number of comparisons. Complexity for this algorithm is O(n*m), where n is the number of chunks (time range / interval size) and m is the number of booking rows within the given time range. I'll hazard that in practice, n >> m, meaning that what really matters to the computation time is the number of intervals. This should be a non-issue, as long as you use sane timeframes and your DB is indexed and maintained correctly. For example, using an interval size of one second for the time range in your question (9:00 - 11:30) is only 9000 intervals to inspect. 9000 rows is paltry to an SQL server. I trust this to be performant a lot more than I do using dynamic SQL to emulate recursion.
If the interval size is 50 million times smaller than the time range, then yes, this will take a significant amount of time to run (notice I didn't say perform poorly), because you'll be running a query against 50 million rows. But is querying the maximum bookings for every millisecond in a twelve hour span (43.2 million ms) reasonable and necessary? There are only 604800 seconds in a week. Performing a query on a set that size, while not trivial, shouldn't give an SQL server any difficulty.
What does your data look like? How fine of an inspection period do you need? If there's a two minute (or second, decasecond, millisecond...) interval where there are 105 bookings instead of 100 because someone entered an "unusual" end time, will that destroy the integrity of your report, or can that data be discarded as noise? I can't answer these questions, but some simple data and requirements analysis on your part can.
Best Answer