Yes, that would work well, we might not normalise each sensor just yet, but we will certainly be heading towards that depending on growth and sensor data.

We will stick with our own custom timestamp as the system works and we don't want to change the firmware on our controllers just yet 😉 In the future though I imagine we may want to make the shift.

We have systems internationally we are going through everything else in the system and writing a PHP layer to handle timezones and store everything in UTC with a separate field to store the time zone of a system, user etc. (Three of us had a 4 hour meeting to flesh out all the scenarios! We have decided to standardise before we start making major changes and get more than our current handful of international users.)

I imagine that once manual table partition becomes hard to manage we will upgrade our SQL Server to an edition that supports it. This interim solution at least prevents the problems of "One Big Table" and "So Many Tables To Keep Optimised"!

In the meantime I reorganised a few indexes today (freeing 400MB), with many more to do (or remove as they are not used), we have very little relational integrity constraints and a lot of the fields have the inappropriate datatype for what they hold!

At least sounding out my ideas on the largest problem (both in terms of data and mentally), is resolved better in my head!

Many thanks for the help 🙂