Update Table with Network Client Connection Status, I need help with DB design. SQL 2008 Ex R2

Question

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?)

• VipX1: I am going to go with a local version of the device also. If the Device changes status and it's confirmed I will then update the database version with the new status, either Online or Offline. In this way I should get months instead of weeks before my Connections Index is in the millions.

Answer 1

On the Devices table, add two fields: LastStatus and LastDateTimeChecked. Update it whenever you insert records into the Connections table. The Connections is your history, but you shouldn't be hitting it for live reporting queries.

• VipX1: Thanks for your reply and I am going to make those changes to the DB Design. One of my other motivations which I should have mentioned was that I wanted to query the DB as little as possible. I didn't want 1 query every thirty seconds. With your suggestion and a local properties that only commits to DB on Status change-over I should be good enough.