I'm little bit confused when should we create relationship properties. For eg., in the following example, what should I go with, first or second? Also, can you please explain why to choose one over another?
(p:Post {title: 'some title'})-[:CREATED_BY {created_at:timestamp()}]->(u:User)
(p:Post {title: 'some title', created_at:timestamp()})-[:CREATED_BY]->(u:User)
Great question. First, these two different examples mean different things.
The first one (created_at on the relationship) means that the relationship was created at that time. Is that what you mean? That first one says nothing about when the post or the user was created. Example: on medium, I can write a draft post. It gets created at 2pm. It doesn't get posted until 4pm. In this case, the post created_at could be 2pm, but the CREATED_BY time could be 4pm, according to when it was published, or when the fact of its creation was published.
The second one means that the Post was created at that time. I'm pretty sure this is the one you mean, and for that semantic reason, it's better.
There's a second reason why putting the property on the node is better. Neo4j doesn't do indexing on relationship properties. So if you want to speed up a query that needs to look at a whole lot of posts based on their creation time, you'd want it on the post, so you could index that property.