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
