Graph Data Model and Cypher Query - How to process ownership/licensing with multiple hierarchies?

[mehrdad.orang]

I would highly appreciate if you can advise me on whether/how the following problem can be solved using Neo4j and Cypher:

Business Problem:

We have ownership rights to some products in different territories and different segments. We have already licensed some of these products in some territories and some segments. Now we want to find which products in which territories and segments are still open to licensing (not licensed yet).

Caveats:

Both territory and segments are hierarchical fields and our ownership and licensing records may not be on the same level (e.g for product ABC we have the ownership right for WorldWide (WW) but have licensed it in the US, which is part of North America (NA) which is part of WW).

Sample Scenario:

Available tables/Data

Product
product_id, name
1, ABC

Ownership
ownership_id, product_id, territory, segment
1, 1, WW, A

License
license_id, product_id, territory, segment
5, 1, US, A1

Territory
territory_name, parent_territory_name
WW, 0
NA, WW
EU, WW
US, NA
CA, NA
FR, EU
UK, EU

Segment
segment_name, parent_segment_name
All, 0
A, All
B, All
C, All
A1, A
A2, A
A3, A

Expected results of final Cypher query that shows product availability in territory and segments:
product_id, territory, segment
1, EU, A
1, CA, A
1, US, A2
2, US, A3

[ameyasoft]

Yes, you can use Neo4j to achieve your desired results. Here is a solution.

Cypher to create the graph:

merge (t:Territory {name: "0", hierarchy: "parent"})

merge (t0:Territory {name: "WW", hierarchy: "child"})

merge (t1:Territory {name: "NA", hierarchy: "child"})

merge (t2:Territory {name: "EU", hierarchy: "child"})

merge (t3:Territory {name: "US", hierarchy: "child"})

merge (t4:Territory {name: "CA", hierarchy: "child"})

merge (t5:Territory {name: "FR", hierarchy: "child"})

merge (t6:Territory {name: "UK", hierarchy: "child"})

merge (t)-[:CHILD]->(t0)

merge (t0)-[:CHILD]->(t1)

merge (t0)-[:CHILD]->(t2)

merge (t1)-[:CHILD]->(t3)

merge (t1)-[:CHILD]->(t4)

merge (t2)-[:CHILD]->(t5)

merge (t2)-[:CHILD]->(t6)

//segment...

merge (s:Segment {name: "0", hierarchy: "parent"})

merge (s0:Segment {name: "All", hierarchy: "child"})

merge (s1:Segment {name: "A", hierarchy: "child"})

merge (s2:Segment {name: "B", hierarchy: "child"})

merge (s3:Segment {name: "C", hierarchy: "child"})

merge (s4:Segment {name: "A1", hierarchy: "child"})

merge (s5:Segment {name: "A2", hierarchy: "child"})

merge (s6:Segment {name: "A3", hierarchy: "child"})

merge (s)-[:CHILD]->(s0)

merge (s0)-[:CHILD]->(s1)

merge (s0)-[:CHILD]->(s2)

merge (s0)-[:CHILD]->(s3)

merge (s1)-[:CHILD]->(s4)

merge (s1)-[:CHILD]->(s5)

merge (s1)-[:CHILD]->(s6)

//Products

merge (p:Product {product_id: 1, name: "ABC"})

merge (p1:Product {product_id: 2, name: "DEF"})

//ownership....

merge (o:Ownership {ownership_id: 1})

merge (o)-[:OWNERSHIP]->(p)
merge (o)-[:OWNERSHIP]->(p1)

merge (p)-[:TERRITORY {product_id: 1}]->(t0)
merge (p)-[:SEGMENT {product_id: 1}]->(s1)

merge (p1)-[:TERRITORY {product_id: 2}]->(t2)
merge (p1)-[:SEGMENT {product_id: 2}]->(s2)

//license....

merge (l:License {license_id: 5, product_id: 1})
merge (l1:License {license_id: 6, product_id: 2})

merge (l)-[:LICENSED]->(t3)
merge (l)-[:LICENSED]->(s4)

merge (l1)-[:LICENSED]->(t2)
merge (l1)-[:LICENSED]->(s2)

Result:

Show Territories that were licensed:

match (t:Territory)-[*..5]->(c:Territory)
where t.name = "WW"
optional match (c)-[:LICENSED]-(l:License)
return t, c, l

Result:

Same with segments:

match (t:Segment)-[*..5]->(c:Segment)
where t.name = "All"
optional match (c)-[:LICENSED]-(l:License)
return t, c, l

Result:

Hope this will help you.,