Database design for monitoring status of applications’ functionalities

I’m creating the database for monitoring status of applications’ functionalities. The logic is following:

Each application has its own, specific list of functionalities that I’m monitoring. Each functionality belongs to only one application. There is a Functionality table that has foreign key to Application

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

The actual monitoring is about querying ApplicationInstance. If there is a problem, information about it goes to AppInstanceError table, wich holds foreign key to ApplicationInstance. If the query is successful, we get a list of statuses of each functionality. So we have a FunctionalityStatus table with foreign keys to ApplicationInstance & Functionality.

I think this is kind of bad design – why do we have multiple reference to Application? What guarantees that both will point to the same Application? Or is there any way to ensure this?

So my proposition of fix is to connect FunctionalityStatus with foreign keys to 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 connection exists and is obvious, so is it OK not to have it in database?

Is there a “propper way” of solving this problem, or of ensuring connections invisible from data design?

To make it more clear I prepared design of DB that I have now:

The only thing that is missing is a connection from FunctionalityStatus to Machine. I see two ways ow making such a connection:

1. Add foreign key to ApplicationInstance – then my doubts are:
   • How to make sure that ApplicationId from Functionality is the same that one from ApplicationInstance?
   • Isn this data duplication really needed?
2. Add foreign key to Machine – and doubts:
   • Will there be a propper ApplicationInstance record for every FunctionalityStatus record?
   • If there is an obvious connection between ApplicationInstance and FunctionalityStatus (mentioned in first doubt) whu can’t we see it in database?
   • Again data redundancy becouse all ApplicationInstance records are (or should be) visible in FunctionalityStatus table

Or maybe the whole design is screwed up and I should figure out something totally else?

Answer

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

If you want to ensure that FunctionalStatus and ApplicationStatus refer to the same application, you could add a new column FunctionalStatus.ApplicationId, and make the foreign key from FunctionalStatus to ApplicationStatus include ApplicationId. Likewise for the 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)
    );