Database design advice: How to avoid deep query?

I am working on a game with a following graph:


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'}), 

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
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
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.

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'})

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.

1 Like

Sweet. This is exactly what I was looking for!