I would like to start using InfluxDB, and would appreciate some help understanding how to organize the data. It will be for a web application which will allow users to archive and retrieve building environmental data such as floats Room 123 temperature, Room 321 temperature, Room 123 humidity, AHU-1 supply temperature, etc, and Booleans Room 123 door status, AHU-1 run status, etc.
There will be multiple users, and each of their data is independent, and will never be queried together.
Each user will solely be responsible to come up with the description of their data, and Room 123 temperature for one user is completely unrelated to Room 123 temperature of another user.
The data is received real time, and every piece of data will likely have a different timestamp.
Typical queries will be like the following.
- Get the average temperature of Room 123, maximum humidity of Room 123, and average temperature of Room 321 between 1 year ago and 2 years ago for User1.
- Get a trend of 100 average values for Room 123 temperature, Room 123 humidity, and Room 321 temperature between 1 year ago and 2 years ago for User1.
Note that the data will be inserted and queried via a script application and not directly by a user, and thus I don’t need intuitive names and can make them “value_1”, “value_2”, “value_3”, etc where 1, 2, 3 come from a traditional SQL database.
- Should I use a separate database for each user or include a tag for user in each measurement?
- Should I use a separate field for temperature, pressure, humidity, etc, or just a common field for “analog values” and a second field for “Boolean values”?
- What other tags should I consider? I contemplated making one for location like “User1_Room123”, but don’t think users will typically have more than one measurement for a given location and when they do, will not want the complexity of having to associate the measurement with the location.
- Assuming exact timestamps are not needed, would it be beneficial from a performance standpoint to force the inserts to coincide on some common periods like ever whole second?
- Or am I looking at this all wrong? If so, how would you recommend organizing the data?
I hope I am not asking too much. If so, are there any consultants available who I can pay to help out? I don’t have a huge budget, but always believe getting the database right up front is one of the most important things I can do.