Development of a database for monitoring the state of application functionality

Question

I am creating a database to monitor the state of application functionality. The logic is as follows:

Each application has its own list of features that I track. Each functionality belongs to only one application. There is a Functionality table that has a foreign key to the Application

Each application runs on one or more machines. Each machine can run one or more applications. This is an MTM connection, so there is a connection table for ApplicationInstance Applications with the machines.

Actual monitoring concerns ApplicationInstance queries. If a problem occurs, information about it enters the AppInstanceError table, which contains the foreign key for ApplicationInstance. If the request is successful, we get a list of the statuses of each function. So, we have a FunctionalityStatus table with foreign keys for ApplicationInstance & Functionality.

I think this is a bad design - why do we have some links to the application? What ensures that both will point to the same application? Or is there a way to provide this?

So my suggestion to fix this is to connect FunctionalityStatus with foreign keys for Machines & Functionality. But in this case, they define ApplicationInstance, so what is the guarantee of having ApplicationInstance for each pair? Shouldn't they be connected somehow? In the real world, the connection exists and is obvious, so what is normal if it is not in the database?

Is there a “better way” to solve this problem or provide invisible connections when designing data?

To make it clearer, I prepared the database design that I now have:

The only thing missing is the connection between FunctionalityStatus and Machine. I see two ways to make such a connection:

1. Add a foreign key to ApplicationInstance - then my doubts are as follows:
   • How to make sure that ApplicationId from Functionality is the same as from ApplicationInstance?
   • Is this duplication of data really necessary?
2. Add a foreign key to the car - and doubts:
   • Will there be a more accurate ApplicationInstance entry for each FunctionalityStatus entry?
   • If there is an obvious connection between ApplicationInstance and FunctionalityStatus (mentioned in the first doubt), why can't we see it in the database?
   • Again, data redundancy, because all ApplicationInstance records (or should be) are visible in the FunctionalityStatus table

Or maybe the whole design is screwed up, and I have to find out something completely different?

Answer 1

Your design seems beautiful to me. I would go for option 1 by adding a foreign key from FunctionalStatus to ApplicationInstance .

If you want to make sure that FunctionalStatus and ApplicationStatus are related to the same application, you can add a new FunctionalStatus.ApplicationId column and make a foreign key from FunctionalStatus to ApplicationStatus include ApplicationId . Similarly for a foreign key from FunctionalStatus to Functionality .

In other words, something like

 CREATE TABLE application ( application_id INT PRIMARY KEY /* Other columns omitted */ ); CREATE TABLE application_instance ( application_instance_id INT PRIMARY KEY , application_id INT REFERENCES application(application_id) , machine_id INT REFERENCES machine(machine_id) /* Other columns omitted */ ); CREATE TABLE functionality ( functionality_id INT PRIMARY KEY , application_id INT REFERENCES application(application_id) /* Other columns omitted */ ); CREATE TABLE functionality_status ( functionality_status_id INT PRIMARY KEY , application_id INT REFERENCES application(application_id) , functionality_id INT /* Part of composite foreign key, see below */ , application_instance_id INT /* Part of composite foreign key, see below */ /* Other columns omitted */ FOREIGN KEY (functionality_id, application_id) REFERENCES functionality(functionality_id, application_id) FOREIGN KEY (application_instance_id, application_id) REFERENCES application_instance(application_instance_id, application_id) ); 

Answer 2

The biggest problem that can arise is that you can always have the same instance identifiers for two different instances of the same application on the same computer. It cannot be at the same time, instance identifiers are reused over time, and there is little chance that your application will get the same again.

When I do this, I assign each application a GUID when it starts, which makes it impossible to have two applications with the same GUID, and then I use this GUID for relationships. You do not even need to have information about the machine in a relationship, since each machine will never make the same GUID as any other machine.

I realized by answering this that I really did not answer your real question. If you want to check whether a certain functionality works in a certain way, it is best to associate it with a machine and an application where the functionality does not fit the way you want, or it is difficult for you to find which one works correctly and which one is wrong.

Having three tables, one for machines, one for applications, and one for functionality would be a better database design. Depending on what you are doing, it can be easier and faster for software to duplicate all the information about the application and the machine for each set of functionalities that you work with, especially if the information about the machine and the application is just one field. You really do not want to slow down the functionality for recording this information if you can help it, so you want to quickly execute it.

Answer 3

If that were the case, I would do it:

• Create 5 tables, Application, Functionality, ApplicationPool and Log.
• Put the FK column in Functionality, this is the application identifier Functionality exists for.
• ApplicationPool will have a Machine Identifier, an Application Identifier column, a Primary Key, which is either a GUID or a sifted identity, ApplicationInstance Identifier, which will be your ApplicationName + PK. If you can do this, I would call your applications your machines.
• Finally, I would make a log table and give an FK column that refers to the ApplicationPool PC. Then, every time you register something, you can add it to the log table, and all your information about the application will be stored separately.

If this is not close, let me know, because I might misunderstand what you are looking for.