Neo4j - 24/7 Casino roster

Question

This is a series of new questions in response to Kenny's answer on my previous question on how to model a database around a 24/7 casino roster. Database model for a 24/7 Staff roster at a casino

Still trying to get my head around graphs and how the data is traversed/connected. With the time graph at the top half of the image in Kenny's answer, does each year have 12 month nodes that then point to a row of day nodes where there is more then one day node with a value of 1 for the 1st day in a month? Would I build these subgraphs for each year or would I use a specific query that adds a node(s) that does not exist as time progresses? I understand there is a query that can do such but it would need to account for the last day in the month node and create the end relationship correct? Will I have any issues with leap years or daylight savings?

On the lower half of the answer image are the game nodes, do they only have one employee and location relationship? I'm not sure how I could tell which employee is assigned to which table(without adding properties to the relationship edges), should I be adding properties to the edges or should I be using a separate node for each pair?

I've made a rough image to show what the pen/paper roster looks like, it may be helpful in some way.

I've also tried to plan out a graph with some questions(red boxes), it was done in illustrator and got a bit messy, I'd love to know if Kenny's graph image was done in a particular application and if so which one, though I take it graphs visually tend to get tangled and messy.

It doesn't seem that you can click the image for a direct link where the text is readable, you can view it here: https://i.stack.imgur.com/4jTZl.png

If it helps I could add the questions text here or recreate the graph with suggested software.

Answer 1

Here is the original image from Database model for a 24/7 Staff roster at a casino:

With the time graph at the top half of the image in Kenny's answer, does each year have 12 month nodes that then point to a row of day nodes where there is more then one day node with a value of 1 for the 1st day in a month?

Yes, that's correct. I've modeled this in the image below.

Would I build these subgraphs for each year or would I use a specific query that adds a node(s) that does not exist as time progresses? I understand there is a query that can do such but it would need to account for the last day in the month node and create the end relationship correct?

I've created a Cypher script that creates the multilevel time index for year, month, day, hour. You can find that here:

https://gist.github.com/kbastani/8519557

For example, to create all the hours in a day, we can merge any day and then later add the year structure. This is one of the advantages of the MERGE Cypher statement. The Cypher query for creating the multilevel date time index is in a way "fault tolerant", if you run it multiple times then it is not going to create duplicates or mess up your data structure.

Here is the Cypher script for merging a 2-level hierarchy of day and hour:

// Enter the day you would like to create
WITH { day: 18, month: 1, year: 2014 } as dayMap

// Merge hours in a day
MERGE (thisDay:Day 
       { 
           day: dayMap.day, 
           month: dayMap.month, 
           year: dayMap.year 
       })

// Get the first hour in the day (hour 1)
MERGE (firstHour:Hour 
       { 
           day: dayMap.day, 
           month: dayMap.month, 
           year: dayMap.year, 
           hour: 1 
       })

// Connect this day to first hour
CREATE (thisDay)-[:FIRST]->(firstHour)

// For each i in (2-24)
FOREACH (i IN tail(range(1, 24)) |

    // Get this hour
    MERGE (thishour:Hour 
           { 
              day: dayMap.day, 
              month: dayMap.month, 
              year: dayMap.year, 
              hour: i 
           })

    // Get the hour before this hour
    MERGE (lasthour:Hour 
           { 
              day: dayMap.day, 
              month: dayMap.month, 
              year: dayMap.year, 
              hour: i - 1 
           })

    // Link the hours
    CREATE (lasthour)-[:NEXT]->(thishour))

// Get the last hour in the sequence (hour 24)
MERGE (lastHour:Hour 
      { 
           day: dayMap.day, 
           month: dayMap.month, 
           year: dayMap.year, 
           hour: 24 
      })

// Connect this day to the last hour
CREATE (thisDay)-[:LAST]->(lastHour)

Will I have any issues with leap years or daylight savings?

No, this should not be an issue but may depend on the questions you are asking. This is context specific. Since we are not depending on the data structure to build a calendar in a user interface, but instead only answer specific questions about a specific day, the rules are inherited from your imported data.

On the lower half of the answer image are the game nodes, do they only have one employee and location relationship? I'm not sure how I could tell which employee is assigned to which table(without adding properties to the relationship edges), should I be adding properties to the edges or should I be using a separate node for each pair?

There is one employee node and one location node. This way you can start from either the employee node or the location node to understand certain things about that object and how it relates to other objects.

I've also tried to plan out a graph with some questions(red boxes), it was done in illustrator and got a bit messy, I'd love to know if Kenny's graph image was done in a particular application and if so which one, though I take it graphs visually tend to get tangled and messy.

The tool that I use to generate images of graph data models is http://www.apcjones.com/arrows/#

To connect nodes, just highlight your mouse pointer over the outside of a node's circle and click and drag the relationship to another node. This little app is open source and only works in Chrome browser.

As for your full data model, I've constructed a Cypher dataset example because I really thought your domain was interesting. You can find those queries here: https://gist.github.com/kbastani/8529380

Here is the data model I used:

And going back to your previous post, you had the question:

What staff have been on the floor for 80 minutes or more on a specific day?

Here is the Cypher query to answer that question:

// What staff have been on the floor for 80 minutes or more on a specific day?
WITH { day: 18, month: 1, year: 2014 } as dayMap

// The dayMap field acts as a parameter for this script
MATCH (day:Day { day: dayMap.day, month: dayMap.month, year: dayMap.year }),
      (day)-[:FIRST|NEXT*]->(hours:Hour),
      (hours)<-[:BEGINS]-(game:Game),
      (game)<-[:DEALS]-(employee:Employee)

WITH game, employee

ORDER BY game.timestamp DESC
WITH employee, head(collect(game)) as game

MATCH (game)<-[:CONTINUE*]-(games)

WITH employee.firstname as first_name, 
     employee.lastname as last_name, 
     SUM(games.interval) as time_on_floor

// Only return results for staff on the floor more than 80 minutes
WHERE time_on_floor > 80
RETURN first_name, last_name, time_on_floor