Algorithm to display schedule?

h4x0rmx

I have some data in the database that represents the schedule for some workers at my company. It looks something like this:

Name       In             Out
Williams   00:00          8:00
Flores     8:00           15:00
Chernova   16:00          22:00
...

I need to display it like in the screenshot attached. I wanted to get your input for the algorithm. This is my idea, I'm sure it can be improved.

Create a row
for each day{
	for each record{
		for each row{
			if space available in row{
				put agent in space and mark used
			}else if no more rows{
				create new row and place agent and mark used
			}else{
				continue;
			}
		}
	}
}

There might be more than one record for an agent each day (sometimes they can come in early in the morning for a little bit and then come back in the afternoon/evening). I have to be able to mark 30mins lunch breaks for longer shifts. I have to be able to mark when a user takes a shorter break. Also, shifts won't necessarily start/end every half hour.
This algorithm could take a lot of time to run if I have a lot of users and want to display the schedule for one whole week (7 = max number of days I'm going to allow to display).
All these constraints make me think about a good way to represent the data. A table would seem to be the logical way, but it might need a lot of calculations and html.

In sum, could you give me your opinion on:
1. my algorithm. How would you improve it? What other algorithm would you use?
2. How would display the actual schedule? table/css/other? (is there any cool html5 thing that could help?)

I'll really appreciate your help!😕

johanafm

It doesn't really matter how long it takes to process unless it is constantly changing. Otherwise, you can just cache the result. You cache the whole table (or the data for it), each day separately, each agent separately, or each day and agent separately.

Every time you make changes for a day/agent/schedule table, just clear out the cache.
When you display the table, start by checking it it's cached and only rebuild it if it's not.

The cache itself could be anything, such as memcached or html files.

h4x0rmx

johanafm;10960682 wrote:
It doesn't really matter how long it takes to process unless it is constantly changing. Otherwise, you can just cache the result. You cache the whole table (or the data for it), each day separately, each agent separately, or each day and agent separately.

Every time you make changes for a day/agent/schedule table, just clear out the cache.
When you display the table, start by checking it it's cached and only rebuild it if it's not.

The cache itself could be anything, such as memcached or html files.

Thanks! I'm looking into memcache, I might have some questions later

h4x0rmx

johanafm;10960682 wrote:
It doesn't really matter how long it takes to process unless it is constantly changing. Otherwise, you can just cache the result. You cache the whole table (or the data for it), each day separately, each agent separately, or each day and agent separately.

Every time you make changes for a day/agent/schedule table, just clear out the cache.
When you display the table, start by checking it it's cached and only rebuild it if it's not.

The cache itself could be anything, such as memcached or html files.

I think I'll opt for using html files. There will be only 8-10 possible queries that will need to be cached, all other queries can be served rather quickly, I believe. I think that we're going to be storing only the data for the current week, but what if they decide to change and keep a history of past data? Would you store everything still in files? or would you store the data in the DB?

As far as displaying the data, do you think that tables is the way to go?

johanafm

h4x0rmx;10960735 wrote:
but what if they decide to change and keep a history of past data? Would you store everything still in files? or would you store the data in the DB?

If you have a working DBMS, sure, why not use it? Store your data in a table and add a date field for when it's added, or perhaps even keep two fields delimiting the time period for the table. This way, it will be easy to retrieve data for a specific time later on, and equally easy to create a script to browse your history.
If you do not have a DBMS, you could of course also store your history as serialized data, complete or partial html pages, or whatever you think works for your purposes. If you go down this road, I'd put the files in a directory called schedule_backup or som such, and use dates as file_names. Or perhaps keep date subdirectories and name the files after your agents.

h4x0rmx;10960735 wrote:
As far as displaying the data, do you think that tables is the way to go?

Yes. The common "tables are evil" comments that you may have seen on the internet concerns using table for layout purposes rather than semantic purposes. But it seems to me that you are indeed dealing with tabular data, so tables is the way to go.

h4x0rmx

Thanks! I think I have a better idea of what I need to do