Best Practice: Equipment Schedule DB design

I'm looking for ideas on the best way to manage a Perspective Equipment Schedule component with a database source.

Are you storing start times and durations, or start times and end times, or something else?

Are you generating the schedule dataset purely in SQL, or with a script transform? Best I can tell, it would need to be a SQL script and not just a named query to do this without scripting.

EDIT
Just to expand, I'm looking at scheduling trucks between our different plants, where each driver would be an item in the component. Updating, inserting, and moving events then reflecting that back in the DB is what I'm mulling over.

2 Likes

I guess I'm leaning towards a named query with a script transform, or some Integration Toolkit iteration magic, if possible, to create the end times for the equipment schedule.

It seems to be the simplest to use a set starting date with a duration. Then changing the schedule should be just changing the event's duration value and refreshing.

Starting Data
row | start                        | itemId  | eventId | estimated_time | origin | destination | label
------------------------------------------------------------------------------------------------------
0   | Wed Nov 20 06:00:00 EST 2024 | Jeremy  | 28      | 30             | 4      | 3           | 42   
1   | Wed Nov 20 06:00:00 EST 2024 | Jeremy  | 29      | 30             | 1      | 4           | 234  
2   | Wed Nov 20 06:00:00 EST 2024 | Jeremy  | 30      | 30             | 1      | 4           | 221  
3   | Wed Nov 20 06:00:00 EST 2024 | Steve   | 31      | 30             | 1      | 4           | 224  
4   | Wed Nov 20 06:00:00 EST 2024 | Steve   | 32      | 30             | 1      | 4           | 602  
5   | Wed Nov 20 06:00:00 EST 2024 | Steve   | 33      | 30             | 1      | 4           | 623  
6   | Wed Nov 20 06:00:00 EST 2024 | Kenny   | 34      | 15             | 1      | 4           | 627  
7   | Wed Nov 20 06:00:00 EST 2024 | Brandon | 35      | 30             | 1      | 4           | 628  
8   | Wed Nov 20 06:00:00 EST 2024 | Steve   | 36      | 30             | 1      | 4           | 629  

# get unique driver names
drivers = list(set(datasetIn.getColumnAsList(datasetIn.getColumnIndex('itemId'))))
headers = ['itemId','eventId','label','startDate', 'endDate', 'leadTime']
data = []

# recurse the input data and create start/end times per driver
for driver in drivers:
	start = None
	newRow = []
	for row in datasetIn:
		if row['itemId'] == driver:
			if not start:
				start = row['start']
			end = system.date.addMinutes(start, int(row['estimated_time']))
			data.append(
				[row['itemId'], row['eventId'], row['label'], start, end, 0]
			)
			start = end
dataOut = system.dataset.toDataSet(headers, data)

output

row | itemId  | eventId | label | startDate                    | endDate                      | leadTime
--------------------------------------------------------------------------------------------------------
0   | Brandon | 35      | 628   | Mon Nov 25 06:00:00 EST 2024 | Mon Nov 25 06:30:00 EST 2024 | 0       
1   | Kenny   | 34      | 627   | Mon Nov 25 06:00:00 EST 2024 | Mon Nov 25 06:15:00 EST 2024 | 0       
2   | Jeremy  | 28      | 42    | Mon Nov 25 06:00:00 EST 2024 | Mon Nov 25 06:30:00 EST 2024 | 0       
3   | Jeremy  | 29      | 234   | Mon Nov 25 06:30:00 EST 2024 | Mon Nov 25 07:00:00 EST 2024 | 0       
4   | Jeremy  | 30      | 221   | Mon Nov 25 07:00:00 EST 2024 | Mon Nov 25 07:30:00 EST 2024 | 0       
5   | Steve   | 31      | 224   | Mon Nov 25 06:00:00 EST 2024 | Mon Nov 25 06:30:00 EST 2024 | 0       
6   | Steve   | 32      | 602   | Mon Nov 25 06:30:00 EST 2024 | Mon Nov 25 07:00:00 EST 2024 | 0       
7   | Steve   | 33      | 623   | Mon Nov 25 07:00:00 EST 2024 | Mon Nov 25 07:30:00 EST 2024 | 0       
8   | Steve   | 36      | 629   | Mon Nov 25 07:30:00 EST 2024 | Mon Nov 25 08:00:00 EST 2024 | 0  

Every time I've used this component, the underlying data model has always had a start timestamp and an end timestamp - I've never even thought about / thought through the implications of just having a start timestamp and an expected duration. For this component, I think your approach works better because the ES component won't show events that don't have an end date - for you, that would be when the truck arrives at another plant, so in the time that its driving, you wouldn't get anything on the schedule.

You could presumably then just save an expected end date, which will be pretty much what you have now (then, adjust when actual is defined).

For me, most of the time based procedures and scripts I have expect a start and end timestamp, so that is how I always have done things, but this is an approach that sounds like it works well for you.

1 Like

That was my thoughts as well. I could initially load the end time as null, use duration as a way to estimate the end time, then update later with actual end time.

That way I can analyze projected verses actual as well.

2 Likes