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.
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 :-
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.
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