SQL database design problem.

Question

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 1

Biggest problem you might have is that it is always possible to have the same Instance IDs for two different instances of the same application on the same machine. The can't be at the same time, Instance IDs are reused over time and there is a small chance that your app will get the same one again.

When I do this kind of thing, I assign each application a GUID when it starts that makes it impossible to have two applications with the same GUID and I then use that GUID for the relationships. You don't even need to have the machine information in the relationship as each machine will never make the same GUID as any other machine.

I realized after answering this that I really did not answer your real question. If you are looking to see if certain functionality is working in a certain way, it is best to related it to the machine and the application where the functionality is not going the way you want or you have trouble finding which one is working right and which one wrong.

Having three tables one for machines, one for applications and one for functionality would be the best database design. Depending on what you are doing, it might be easier and faster for the software to duplicate all the application and machine information for each set of functionality you are working with, especially if the information about the machine and application is just one field anyway. You really don't want to slow down the functionality for the logging of this information if you can help it, so you want to have it done quickly.

Answer 2

If it was me this is how I would do it:

1. Create 5 tables, Machine, Application, Functionality, ApplicationPool, and Log.
2. Put a FK column in Functionality, that is the ID of the Application the Functionality exists for.
3. ApplicationPool would have a Machine ID column, an Application ID column, Primary Key that is either a GUID or a Seeded Identity, an ApplicationInstance ID which would be your ApplicationName + PK. If you can this is what I would use to name your applications your machines.
4. Finally, I would make the Log table and give an FK Column that references the PK of ApplicationPool. Then every time you logged something you could add it to the Log table and all of your information about the application would be stored separately.

If this isn't close let me know because I could have misunderstood what you are looking for.

Answer 3

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)
    );