relationships.ini for an indirect master-child relationship

[rleyba]

Hi Team,

I have an SQL relationship question where I need to relate two indirectly linked tables together.
I have a mySQL database which is composed of 3 main tables and they are linked many-to-many. See attached diagram.

The first one is an IT APPLICATIONS table where an IT Application can reside on one on more SERVERS. These servers, in turn have multiple network cards each with its own IP addresses. In our actual setup, an application should only be linked to ONE IP address per server. In my example scenario, only IP addresses 10.1.1.1 and 10.1.1.4 should be linked to the APP-A application. I am missing a relationship parameter here somewhere, but I am not sure how to setup the tables to link the APPS table and the IP_ADDDRESES table.

Initially, I created an APPS2IP table to do the many-to-many relationship, but it could lead to a situation where an IP address (say 10.1.1.7) below might link to APP-A but would result in a constraint violation, because 10.1.1.7 is connected to SERVER-C, which is NOT linked to APP-A.

How could I prevent this scenario from happening?

Thanks very much.

Relationships table.

relationship.JPG (91.74 KiB) Viewed 4088 times

[rleyba]

Hi Team,

I may have found the solution. I have changed the relationship so that the APPS table now links directly to the IP_ADDRESS table and the SERVER table now hangs off from that. See my new diagram below.

The next scenario is that the IP address is not unique, so I am looking for the procedure to create a trigger so that once I attach an IP address to a server, then the trigger populates the IP address table with the servername that the particular active record was attached to. See my other diagram, where the servernames are blank as I am not sure how to set the trigger to populate it. I am using mySQL.

Thanks for all the help.

[rleyba]

Hi Steve,

Could you kindly direct me to an actual example of how to invoke triggers in Xataface to help me achieve what I need to do above? thanks and Happy New Year.