I am creating the database for monitoring status of applications' benefits. The logic is following:
Each application features its own, specific listing of benefits that I am monitoring. Each functionality goes to simply one application. There's a Functionality table which has foreign answer to Application
Each application operates on a number of machines. Each machine can run a number of programs. This really is MTM connection, so there's ApplicationInstance table connection Programs with Machines.
The particular monitoring is all about querying ApplicationInstance. If there's an issue, details about it is going to AppInstanceError table, wich holds foreign answer to ApplicationInstance. When the totally effective, we obtain a listing of statuses of every functionality. So there exists a FunctionalityStatus table with foreign secrets to ApplicationInstance &lifier Functionality.
I believe this really is type of bad design - so why do we've multiple mention of the Application? What guarantees that both points towards the same Application? Or perhaps is there in whatever way to make sure this?
So my proposition of fix would be to connect FunctionalityStatus with foreign secrets to Machines &lifier Functionality. However in this situation they define ApplicationInstance what exactly may be the guarantee of getting ApplicationInstance for every pair? Should not they link in some way? Within the real life connection is available and it is apparent, same with it OK not have it in database?
It is possible to "propper way" of fixing this issue, or of making certain connections invisible from data design?
To really make it more obvious I prepared style of DB which i have finally:
The only real factor that's missing is really a connection from FunctionalityStatus to Machine. I see two ways ow making this type of connection:
- Add foreign answer to ApplicationInstance - then my doubts are:
- How you can make certain that ApplicationId from Functionality is identical that certain from ApplicationInstance?
- Isn this data duplication really needed?
- Add foreign answer to Machine - and doubts:
- Maybe there is a propper ApplicationInstance record for each FunctionalityStatus record?
- If there's an apparent link between ApplicationInstance and FunctionalityStatus (pointed out in first doubt) whu can't we have seen it in database?
- Again data redundancy becouse all ApplicationInstance records are (or ought to be) visible in FunctionalityStatus table
Or whole design is messed up and that i should determine something totally else?
Your design appears fine in my experience. I'd choose option 1, adding an overseas key from
If you wish to make sure that
ApplicationStatus make reference to exactly the same application, you could include a brand new column
FunctionalStatus.ApplicationId, making the foreign key from
ApplicationId. Likewise for that foreign key from
Quite simply, something similar to
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) );