I have C# client application that updates a Server Database, SQL 2008 Express R2. The client application send a ping to a group of devices once every 30 seconds and displays their status then updates Database. (updates only on successful response). There is also a web page (from the same server) that can display the current status of each device, getting data from the Database. If a device goes offline the client application tries 4 times to get a response and if it fails 4 times in a row it marks the device offline saying "Offline since DateTime".
I have 2 tables. 1. A Devices table with Name and IP address plus Index/ID. 2. A Connections table with FK: DeviceID and DateTime and Index.
The Index on the Connections table is going to be a massive value in a very short space of time. I don't need all that Data. All I need is the last known Online dateTime for each Offline device.
If you can image my Primary Customer, the owner of these devices will open the Web Page on his IPhone and see: Device 1 to 6 is Online, Device 7 is Offline since 7AM Yesterday etc.
I have 2 possible solutions: A. Hold the last connection value in the application and only update the database if the device goes Offline. (This is error prone and could loose information). B. Create a new Table at Midnight and have Multiple Connections tables separated by Day. (Is this possible? Is it a good idea?)