This repository contains the hardware and software components of a PIR based tracking device to monitor the usage of local bike trails. The data is sent over LoRaWAN to TTN and from there to a Google Cloud backend which stores the data and provides api endpoints for the data visualization web UI.
It would be very nice if we could configure and prepare a device completely without having to know on which trail it will be mounted.
To reach that goal we need to create an abstraction layer between the device id and the trail. (Currently the device id indicates the corresponding trail)
Such a logic could be implemented with the following structure:
every device has a unique id (this is already the case)
we create a table to specify all the trails
we create a connection table that describes which devices were mounted on which trail in a specific time range.
That would allow us to simplify the maintenance process by just replacing the old device with a new one and insert a new entry into the timelineConfig table.
Sorry 😅 I know it's not a relational database but this was the easiest way for me to describe what kind of behavior I would suggest.
Probably easier solution:
Another possibility would be to stamp the trail info into the dataset on the db when the data arrives. With that method we only need one lookup table to get the current "device->trail" association and the query could filter directly on the dataset and does not need to join different information.
I think I would prefer this method but it just came to my mind while I was writing this issue so I'll leave the original idea at the top.
It would be very nice if we could configure and prepare a device completely without having to know on which trail it will be mounted. To reach that goal we need to create an abstraction layer between the device id and the trail. (Currently the device id indicates the corresponding trail) Such a logic could be implemented with the following structure:
That would allow us to simplify the maintenance process by just replacing the old device with a new one and insert a new entry into the
timelineConfig
table.Sorry 😅 I know it's not a relational database but this was the easiest way for me to describe what kind of behavior I would suggest.
Probably easier solution:
Another possibility would be to stamp the trail info into the dataset on the db when the data arrives. With that method we only need one lookup table to get the current "device->trail" association and the query could filter directly on the dataset and does not need to join different information.
I think I would prefer this method but it just came to my mind while I was writing this issue so I'll leave the original idea at the top.