Database design advice: How to avoid deep query?

[deepak]

I am working on a game with a following graph:

(User)-[:CITIZEN_OF]->(Country)<-[:STATE_OF]-(State)<-[:REGISTERED_IN]-(Business)<-[:OWNED_BY]-(Investment)<-[:REPORT_OF]-(Report)

User is given access to each resource if they are CITIZEN_OF the country which holds that resource.
For example, if users want to access a Report, they make an API call: getReport(userId, reportId) which is permitted if userId is a CITIZEN_OF country which has a transitive relation with Report.

Provided I may have maximum 10K nodes at each level, will it hurt to have a deep query like this on each API request?

MATCH (u:User {userId:'userId'})-[:CITIZEN_OF]->(c)<-[:STATE_OF]-(s)<-[:REGISTERED_IN]-(b)<-[:OWNED_BY]-(i)<-[:REPORT_OF]-(r: Report{reportId:'reportId'}), 
RETURN u,r

One alternative is to add a country property in User and Report (and all other resource nodes). This approach has 2 issues:

1. We need 2 read queries, 1 for User and 1 for Report to match their Country property for each API request
2. State can be moved to a different country (Each child can move to a different parent). This operation won't be as common as the first one, but if that happens, I would need to recursively modify country property of all nodes under that State.

Second alternative is to add
(User)-[:HAS_ACCESS_TO]->(Resource)
relation to all the resources like Report, Investment etc. This approach will heavily impact the performance when a new Report is created, adding all users from the country.

Third alternative is adding a new
(User)-[:CITIZEN_OF]->(Country)<-[:PART_OF]-(Resource)
relation from Report to Country. This will have issues when a State moves to a new Country. We need to recursively detach and re-attach all child nodes to the new country. Changing country property feels faster than updating all relations.

I understand the best way to find a solution is to measure all the approaches, but I am hoping for some feedback and industry practices from people who are more experienced.

[michael_hunger · Accepted solution]

One of the approaches of doing such checks is to run a shortest-path query,
if it returns something then it's all good if not then the request is denied.

Or in this particular case you can do:

MATCH (u:User {userId:'userId'})-[:CITIZEN_OF]->(c)
<-[:STATE_OF]-(s)<-[:REGISTERED_IN]-(b)<-[:OWNED_BY]-(i)<-[:REPORT_OF]-(r: Report{reportId:'reportId'})
USING JOIN on c
RETURN u,r

if you PROFILE that query you should see two index lookups from either side with an expand to country and then a node-hash-join to compare the countries.

[deepak]

Sweet. This is exactly what I was looking for!