Tips for modelling a wine database

[oli...@kasual.biz]

Hello, I would like some advices on how to generate a graph data model adapted to a wine sales system.

The different entities are: 

•   each producer (name, address) produces several wines (name, colour)
• each wine is associated with one vintage (year of production)
• purchasers (name, address) propose offers (price, number of bottles) for a wine and a vintage.

How to represent the vintages in this graph: 

• Does vintage have to be a property of relations: producer->[:PRODUCES(year: 2015)]->wine and offer->[:SELLS(vintage: 2015)]->wine ?
• Does each vintage of each wine have to be a node with a "year" property: producer-[:PRODUCES]->wine<-[:LINKS]-vintage(year: 2015) and offer-[:SELLS]->vintage ? In this case, we will have as many nodes 2015 as wines with a 2015 vintage.
• Or must each vintage be a unique node linked to all the wines and offers: producer-[:PRODUCES]->wine-[:FOR]->vintage(year: 2015) and offer-[:SELLS]->wine, offer-[:LINKS]->vintage(year: 2015) ? In this case we will have as many relationships to the vintage node (year: 2015) as wines with a 2015 vintage.

Thank you in advance for your advice: this kind of representation seems complex enough to define to obtain optimal queries.

[Michael Hunger]

I would put each vintage into its own node.

As you want to connect multiple things to each vintage.

Cheers, Michael

--
You received this message because you are subscribed to the Google Groups "Neo4j" group.
To unsubscribe from this group and stop receiving emails from it, send an email to neo4j+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Kamal Murthy]

Hi,

A vintage wine is one made from grapes that were all, or primarily, grown and harvested in a single specified year. For sales system, this can be a property of a wine. Here is my design:

CREATE (c1:Winery {id: "winery1", name: "Napa"})

CREATE (w1:Wine {id: "w1", name: "Red1"})

CREATE (w2:Wine {id: "w2", name: "Red2"})

CREATE (d1:Dealer {id: "DL1", name: "DLR1"})

CREATE (d2:Dealer {id: "DL2", name: "DLR2"})

CREATE (o1:Order {number:'PO100',date:'1/1/2017'})

CREATE (o2:Order {number:'PO200',date:'2/1/2017'})

CREATE (s1:Sold {price:100,qty:20,vintage:2015,ponbr:"PO100"})

CREATE (s2:Sold {price:120,qty:30,vintage:2010,ponbr:"PO100"})

CREATE (s3:Sold {price:90,qty:35,vintage:2000,ponbr:"PO200"})

CREATE (c1)-[:"VINTAGE_WINE"]->(w1)

CREATE (c1)-[:"VINTAGE_WINE"]->(w2)

CREATE (c1)-[:"DEALER"]->(d1)

CREATE (c1)-[:"DEALER"]->(d2)

CREATE (d1)-[:"ORDERS"]->(o1)

CREATE (d1)-[:"ORDERS"]->(o2)

CREATE (w1)-[:"PART_OF_ORDER"]->(s1)

CREATE (w1)-[:"PART_OF_ORDER"]->(s2)

CREATE (w2)-[:"PART_OF_ORDER"]->(s3);

Wines sold with purchase order numbers:

MATCH (c:Winery)-[]->(w:Wine)-[]->(s:Sold)

WITH c, w, s

MATCH (c:Winery)-[]->(d:Dealer)-[]->(o:Order)

WHERE o.number = s.ponbr

WITH c, d, w, s

RETURN c.name, d.name as Dealer, w.name as Wine, s.vintage, s.qty, s.price, s.ponbr;

Filter by vintage year:

MATCH (c:Winery)-[]->(w:Wine)-[]->(s:Sold)

WHERE s.vintage = 2010

RETURN c.name, w.name, s.vintage, s.qty, s.price, s.ponbr;

Every purchase order can also be reconciled by adding 'TotAmt' property to 'Order' node and totaling the wines sold.

Hope this will help you.

-Kamal