I'm designing a system which allows user to analyze their data over time. It is like a IoT database which stores a lot of information from different devices from the user. I don't think that a normal RDBMS will scale to the requirements. What database (model) will best fit my requirements below?
The system needs to handle a large number of writes. Each user will have multiple devices which will each output a measurement, the time range over which this measurement was calculated and a value. These measurements range from 10s to 1h in time span. When there is no measurement, the device does not create any data. I expect about 100.000 and 300.000 measurements across all user at a single moment in time but these will not be written to the database immediately. (Number of measurements will grow with the number of users)
I'm not sure how I should store the measurements. I could store the measurements as a time range:
+------------+ +--------------+ +--------+ | User | | Measurement | | Type | +------------+ +--------------+ +--------+ | * id +-------> | * user_id | <---------+ * id | | * name | | * type_id | | * type | | * group_id | | * value | | * unit | +------------+ | * start_time | +--------+ | * timespan | +--------------+
Note: timespan is in seconds, could also be substituted with end_time.
Or the measurements can be stored as samples over the smallest aggregate possible (10s). When the devices store the values in the database all values are interpolated.
+------------+ +--------------+ +--------+ | User | | Measurement | | Type | +------------+ +--------------+ +--------+ | * id +-------> | * user_id | <---------+ * id | | * name | | * type_id | | * type | | * group_id | | * value | | * unit | +------------+ | * start_time | +--------+ +--------------+
Although the time range option has the smallest storage impact, I think that the sample storage might be easier to aggregate.
The system needs to be able to handle the following types of questions:
What were user with id X measurements of type Y in the last week grouped by day?
What was Andy's room temperature in the last week per day?
How many movies did April watch in the last month per week?
What is the sum of measurements of type X in user group Y in the last month grouped by week?
How many book have been read by the Parks department in the last year by month?
Where do measurements of type X and Y overlap in time (and what is their value) for user with id Z in the past day grouped by hour.
How many minutes was Donna calling while driving her car today (grouped by hour)?
I expect the last question to be the most difficult one because it needs to intersect multiple timelines to get a answer. (The measurements which are matched will be of the same unit, e.g. both time or both degrees centigrade.) This type of question might be one of the most important questions.
Most questions will be over the last days/week/month/year. There will also be questions which will be over a fixed time range.
All these queries need to be fast. (below 1 second)
I did some assumptions which I think will help improve the performance of the system.
- The larger the time range which was queried, the larger the aggregate will be. Querying the room temperature over the last year per hour does not make sense.
- Measurements which are a long time ago do not have a lot of significance. Querying the number of book read on 11th of January 2013 will not be of much importance.
I've looked at some database/storage solutions. So far I have found the following databases but I'm unsure if they will fit our requirements and scale because I do not have any previous experience with working on such a scale.
All solutions take a different approach to schema's and I cannot determine if these options are a good fit because I'm used to working basic RDBMS like MSSQL and MySQL.
So my question is:
Which database system will be the best solution for my problem and will this impact the schema I designed?