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)