Neo4j Best Practice for Index on Relationship Property?

Question

I'm trying to migrate a PostgreSQL database to Neo4j and have the following m-n relationship in PostgreSQL: user-(blocked)-user

So in PostgreSQL I have an extra table "blocked" that has the following columns: userid1, userid2, blockedsince, blockeduntil I also have an index on blocked.blockeduntil to search for rows that eventually must be removed when the blocking is over.

How can I build this relationship including the index in Neo4j?

I already included all user-rows in a node type "user" in Neo4j. Next step would have been to create a relationship called "blocked" from user to user. So basically like this: (user)-[:blocked]->(user) I would have added the 2 relationship properties "blockeduntil" and "blockedsince" to the relationship. But it does not seem to be possible to create an index on the relationship property blocked.blockeduntil

Original code for PostgreSQL:

CREATE TABLE user (
   UserId bigserial NOT NULL PRIMARY KEY,
   ...
);
CREATE TABLE blocked(
   UserId1 bigint NOT NULL references user(UserId),
   UserId2 bigint NOT NULL references user(UserId),
   BlockedSince timestamp NOT NULL,
   BlockedUntil timestamp NOT NULL,
   PRIMARY KEY (UserId1, UserId2)
);
CREATE INDEX "IdxBlocked" on blocked(blockeduntil);

My first approach in Neo4j:

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:///blocked.csv" AS row
MATCH (u1:user {userid: toInteger(row.userid1)})
MATCH (u2:user {userid: toInteger(row.userid2)})
MERGE (u1)-[b:BLOCKED]->(u2)
ON CREATE SET b.blockedsince = localdatetime(replace(row.blockedsince, " ", "T")), b.blockeduntil = localdatetime(replace(row.blockeduntil, " ", "T"));

What is the best practice to achieve this relationship including the index on blockeduntil? Would it be better to create a new node type called "blocked" like this?

USING PERIODIC COMMIT
LOAD CSV WITH HEADERS FROM "file:///blocked.csv" AS row
CREATE (:blocked{userid1: toInteger(row.userid1), userid2: toInteger(row.userid2), blockedsince: localdatetime(replace(row.blockedsince, " ", "T")), 
                      blockeduntil: localdatetime(replace(row.blockeduntil, " ", "T"))});

And then create an index on blocked.blockeduntil like this?

CREATE INDEX ON :blocked(blockeduntil);

During my research I stumbled upon explicit indexes Explicit Indexes but they seem to be deprecated. Also I'm not sure if Full Text Indexes are the right choice here.

Thanks! So following your suggestion I would alter my data model and make a node type "blocked" so I can use blocked.blockeduntil for indexing. — Thomas
According to this community.neo4j.com/t/how-can-i-use-index-in-relationship/1627 post from a Neo4J Staff member, it seems to be best practice to model such entities as nodes, not relationships. So I think I will model "blocked" as a node type. — Thomas

Thomas Thomas · Accepted Answer · 2019-09-11T11:04:39

Ok, it seems that I found an official answer from a Neo4J staff member. https://community.neo4j.com/t/how-can-i-use-index-in-relationship/1627/2

From the post:

We instead recommend refactoring your model. If you need to perform an index lookup of something, that usually suggests that thing would be better modeled as a node. As a node you are able to create the indexes and constraints you need for quick lookup.

So I will model "blocked" as a node and create an index on blocked.blockeduntil.

Neo4j Best Practice for Index on Relationship Property?

1 Answers