Database Modelling: When to create 'middle node' and when to create relationship

[admin3]

So I am about to create a new connection between Store and Products.

The 'connection' is many to many.

Now, this connection also contains other info such as startDate and endDate.

My connection is whether it's better to just create a relationship or if I need to create a middle node.

For example
Version 1
(store)-[:PROMOTES {startDate:...., endDate:...})->(product)

Version 2
(store)-[:PROMOTES)->(promotion:Promotion {startDate:...., endDate:...})<-[:PROMOTED]-(product)

Some info:

• This promotion info will never be fetched 'alone'. You always need store and product too.
• Admin will very often edit it (or ask to edit it) and is an admin of either the product or store i.e. a connection will be checked there. Delete the promotion altogether or edit the dates (which must be validated that there are no conflicts with other promotional dates).
• When fetching a promotion via the product, the dates will be used to fetch other promotions of that store during the dates found
• Active promotions i.e. promotions where startDate<today()<endDate will be often fetched

Hope I make sense.
Thanks

[mdfrenchman]

How you have it works well.

Switch to a node if:

1. The promotion will need to link to a third object (for example, you want to keep history or audit logs on who changed the dates etc)
2. You have A LOT of promotions for a product or a store. And at any point are going to want to filter and show only active promotions. to get active promotions, which I'd think would be a frequent query, will have to filter by the rel property, and that's not using an index.
3. You want to group products into a single promotion. (ex: Promotion campaign is 20% off all fizbucket products) It's easier to connect and manage that by the single Promotion or Campaign node and tie that campaign to specific stores.

Small amount of promotions between store and product, how you have it is fine.
As it grows you'll want an intermediary in my opinion.

Hope that helps (and this formatted correctly despite the preview pane).

-Mike

[ameyasoft]

Check this article:

Medium – 16 Dec 19

Keeping track of graph changes using temporal versioning

In this post we’re going to cover why versioning, how to do time-based versioning in Neo4j and how to deal with bitemporal versioning.

Reading time: 8 min read