Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I've been tasked to design a database to handle a large volume of incoming traffic from sensor readings, possibly 1,000s an hour when it's at full strength. Each reading will be relatively short - about 20 character string including a sensor id number, data value and timestamp.

My initial plan was to create a table with the 3 relevant fields and automated id and run an insert statement for each reading. Then replicate the database to one with more detailed information, i.e. sensor location, which a web portal can access and is less time critical in terms of accessing the data so I can run more complex queries.

I had thought about splitting the raw data into different tables based on the sensor id but I'm not sure whether this would help or just confuse matters. Or perhaps use multiple connection points for the sensors (each sensor can be programmed for this) which connect to multiple databases, to reduce the load on the DB

I plan to use an Azure based MSSQL database to store the data.

I'm hoping to get some pointers whether my design thoughts are good or whether I should be look in a different direction. Also any links to articles that have tackled similar projects would be useful (I haven't found much on the net)

share|improve this question
2  
I think you're worrying too much about 'large volumes' of data, which thousands of records an hour is anything but...Why would you want to split the data into different tables based on the sensor ID? –  Mark Sinkinson Jul 16 at 14:25
    
See this question dba.stackexchange.com/questions/64484/… for some interesting points. –  Max Vernon Jul 16 at 15:13
    
Thanks for the comments and link. If thousands per hour isn't considered 'large volume' then that helps. I was just worried about any considerations in design I should be making. For the table separation, I was thinking when it gets to millions of records in a single table, data retrieval may become time consuming. I don't have any experience on databases/tables of this size so just trying to foresee any issues. –  Chris Crawshaw Jul 16 at 15:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.