Old Pinger and PerfSonar Schema here
New pinger+perfSonar schema is here
Talking to Yee, he points out a few things about the database:
1. You do not want a table to exceed 1 Million rows. For PingER we have 48 measurements / day, roughly 1000 pairs, 365 days in a year or ~ 1.75Million rows/year. Thus perfSONAR shards the data into 1 table per month. Then there is a method to access data spread across multiple tables. PerfSONAR has classes to facilitate this, see for example http://anonsvn.internet2.edu/viewvc/viewvc.py/perfSONAR-PS/trunk/perfSONAR_PS-PingER/lib/perfSONAR_PS/DB/SQL/PingER.pm?view=log and these could be used.
2. The extra columns in data_data (e.g. throughput, MOS, alpha etc.) could simply be appended to the data table (thus not requiring a join).
3. perfSONAR has many methods for calculating IPDV, IQRLPD etc. They are written in Perl. We could use these. Familiarize yourself with http://anonsvn.internet2.edu/viewvc/viewvc.py/perfSONAR-PS/trunk/perfSONAR_PS-PingER/lib/perfSONAR_PS/Utils/PingStats.pm?view=log