Database Schema design of server running status log

Question

I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running PUMA server and another one is running NGINX and so on. I want to store the running status of the instances and their application servers in a database.

Like, If Instance A is running or not, that I can get by continuously hit into the exposed port per second and store it in the database. I am scanning it in each minute. There will be 3 columns I can think of right now,

Instance ID    Running Status   Time

Instance A        Running        10:10:04
Instance A        Running        10:11:04
Instance A        Running        10:12:04

Next, I want to store the status if Application Servers within Instance A running or not. There will be 3 columns I can think of right now,

Let's say PUMA and NGINX are running in Instance A

Instance A :-

Application Name   Running Status   Time

PUMA                  Running       10:10:20
NGINX                 Running       10:10:30
PUMA                  Running       10:11:21
NGINX                 Running       10:11:30

I am using Postgresql. What will be the recommended schema design ? Should I create a table for each instance and how will I map the applications with that particular Instance ? If I take Instance ID as a primary key then duplicate values in that column are not possible.

Answer 1

Whatever you do, you should certainly not be creating a new table for every instance.

The simplest solution would be to just not use Instance ID as the primary key, and instead have an autogenerated primary key and an index on the instance ID:

ID    Instance    Status     Time

1     A           Running    10:10:04
2     A           Running    10:11:04
3     A           Running    10:12:04
4     B           Running    10:10:04
5     B           Running    10:11:04
6     B           Running    10:12:04

And use a separate application table that does the same:

ID    Instance    Application   Status     Time

1     A           PUMA          Running    10:10:20
2     A           NGINX         Running    10:10:30
3     A           PUMA          Running    10:11:21
4     A           NGINX         Running    10:11:30
5     B           PUMA          Running    10:10:20
6     B           NGINX         Running    10:10:30
7     B           PUMA          Running    10:11:21
8     B           NGINX         Running    10:11:30

Etc...