Introduction: I have a question about the design of a project as it relates to databases, and the scale-ability of the design. The project is volunteer, so there is no commercial interest.
But first a bit of background: Background: I have programmed a rasp pi to record radio beeps from wildlife trackers, where the beep rate per minute (bpm) can be either 80, 40, or 30. The rate can only change once every 24 hours. The beeps are transmitted on up to 100 channels and the animals go in an out of range on a given day. This data is written to a Sqlite3 db on the Rpi. Since the beep rate will not change in a given 24 hour period, and since the rasp pi runs on a solar/battery setup it wakes up for 2 hours every day to record the radio signals and shuts down, so for a given 24 hour period I only get 2 hours of data (anywhere between about 5-15,000 beeps depending on beep rate and assuming the animal stays within range). The rpi Sqlite3 DB is sync'd over cellular to a postgresql database on my server at the end of each days 2 hour recording period. Since I am processing radio signals there is always the chance of random interference being decoded as a valid beep. To avoid a small amount of interference being detected as a valid signal, I check for quantity of valid beeps within a given 1 hour window - so for example if the beep rate is 80 it checks that there are 50% of the maximum beep rate detected (ie 80*60*0.5) - if there is only a handful of beeps it is discarded. Database design: The BPM table is very simple: Id Bpm_rate Integer dt DateTime I want to create a web based dashboard for all the currently detected signals, where the dashboard contains a graph of the daily beep rate for each channel (max 100 channels) over user selectable periods from 1 week to 1 year - that query does not scale well if I query the bpm table. To avoid this I have created a bpm summary table which is generated periodically (hourly) off the bpm table. The bpm summary table contains the dominant beep rate for a given hour (so 2 records per day per channel assuming a signal is detected). Does this summary table approach make sense? I have noted that I am periodically syncing from MySQL to the server, and then periodically updating the summary table - its multi stage syncing and I wonder if that makes this approach fragile (although I don't see any alternative). Thanks in advance for any input. Al
