MichaelMao MichaelMao - 6 months ago 17
SQL Question

Notify user history scheme design

I want to notify user when the service down and up and this is my design now (just for demo).

service
{
id,
name,
isActive
}

history
{
id,
serviceId,
upTime,
downTime
}


When service down I will add a record to history with downTime and upTime is NULL. Until service up I will find the last history with relative serviceId
and update the upTime. I do not know it is a right design or not. Maybe separate to two records?

Answer

I would prefer to separate that becomes two records. And I would like to add Comment field that will explain about the problem why the services are down or how do you repair to restore the services.

The schema would be like

Table History

HistoryID int,
ServiceID int,
Status varchar(4),
StatusTime datetime2(3),
Comment varchar(50)

Then, the Data would be like

HistoryID   ServiceID     Status       StatusTime               Comment
1               1         Down         2016-05-03 00:00:00      Problem happens because xxx
2               1         Up           2016-05-04 00:00:00      Restart the service

*EDIT if you want to show something like :

ServiceID: 1 donwtime:2016/05/03 uptime:2016/05/04.

you could use your schema to make the select statement easier. but I still recommend using Comment to let you know what cause the service down, and how can u fix it. It would make it better for maintaining your service. And you can insert a new row for each Down status, and update the last row for Up status. for example :

HistoryID ServiceID  DownTime UpTime  DownComment UpComment
    1         1         xxx    xxx       xxx         xxx
    2         1         xxx    NULL      xxx         NULL

first row means the service was already repaired and went UP at xxx time. second row means the service went down again, but hasnt been repaired.

Comments