Model entity involved multiple relationship

[sethia_arun]

Hi,

I have data in following format

Account	   Employee#	Service Provider
ABC	          X1	             M1
ABC	          X2	
XYZ           X1	
XYZ	          X2	             M1
XYZ	          X3	

I could able to come out with following attached model.

The problem is the model provides wrong information like M1 is providing service to X2 , but for which account? The X2 is associated with ABC and XYZ account.

Thanks
Arun

[Joel]

It seems likely M1actually provides service to the Company not the Employee?

If you need to model it this way though, and X2 doesn't use M1 at both companies you'll have to change your model to capture the context of each employee specific to each account.

New nodes and relationships to capture what you want in the graph.

[sethia_arun]

Thanks @Joel

The idea is X1 is employee of ABC; whereas X2 is employee of ABC and XYZ (dual employment is allowed). The M1 is providing Service to X1 in context of ABC and M1 is providing service to X2 in context of XYZ.

The problem is employee nodes can be shared between multiple companies and employee is getting service in a given company context.

You can think about data in multiple relational tables:

Table 1: Company Master

Company
XYZ
ABC

Table 2: Person Master

Name
X1
X2
X3

Table 3: Company to Employee relationship

Company	   Employee
ABC	          X1	            
ABC	          X2	
XYZ           X1	
XYZ	          X2	           
XYZ	          X3	

Table4: Employee to Service Provider relationship in given context

Account	   Employee#	Service Provider
ABC	          X1	             M1
XYZ	          X2	             M1

I can think about to add additional relationship property "company_for:" part of PROVIDE_SERVICE to find service provided for what purpose/company. But doing that will have impact on Graph Traversal.

Thanks

[Joel]

As I eluded to, if M1 can provide service to X2 in the context of only one company, you'll have to reflect that in the model somehow.

However, in my experience service is normally provided to the company not to an individual employee, so that is how I would look at adjusting the model. Perhaps, something like this (note: this is a graph meta-model, not a graph output like your visual) I put SUPERVISES, for Person->ServiceProvider but maybe that relationship is RECOMMENDED, and/or one or more other relationships.

Otherwise you'll need multiple nodes per person, to reflect their separate work contexts... (and I can't think of a case where that makes sense, unless one could find and track individuals surreptitiously outsourcing their own work to a third party?)

[sethia_arun]

Thanks @Joel

Because of some reason, I can't disclose the business process here; the one I depicted here is not the real one, but a similar concept we have in our current business process (entity names are different). The data that I shared in relational format provides the relationship between entities, entities' names can change (from company to something else or employee to any other term, etc.) or column names, but the relationship will remain constant.

[ameyasoft]

Try this:
MERGE (e:Employee {empnbr: "X1"})
MERGE (e1:Employee {empnbr: "X2"})
MERGE (e2:Employee {empnbr: "X3"})

MERGE (a:Account {accnt: "ABC"})
MERGE (a1:Account {accnt: "XYZ"})

MERGE (a)-[:EMPLOYEE]->(e)
MERGE (a)-[:EMPLOYEE]->(e1)

MERGE (a1)-[:EMPLOYEE]->(e)
MERGE (a1)-[:EMPLOYEE]->(e1)
MERGE (a1)-[:EMPLOYEE]->(e2)

MERGE(s:ServiceProvider {name: "M1"})
MERGE (s)-[:PROVIDER {accnt: "ABC"}]->(e)
MERGE (s)-[:PROVIDER {accnt: "XYZ"}]->(e1)

RETURN *

Result:

MATCH (b:Employee)-[r:PROVIDER]-(c:ServiceProvider)
RETURN b, c

MATCH (b:Employee)-[r:PROVIDER]-(c:ServiceProvider)
where r.accnt = "ABC"
RETURN b, c

[sethia_arun]

Thanks.

I think only way we can avoid such star relationship problem by using additional property part of relationship like you used part of "PROVIDER" relationship.

[ameyasoft · Accepted solution]

Yes, in these scenarios I found adding a property is the best option. This solution worked for me even in production environments.