I want to set up a system where freelance employees can say on which days they can work. They can also say if they can work in the early or the late shift. The manager can input a period (start date, end date) and see which employees are available for work. This is a fairly simple system.

I was thinking of setting it up like this:

etc...

This works, but although I am not a newbie anymore I miss the expertise to judge if this is a good solution. I think if you have 500 employees that can each work 100 days of the year on both shifts, you have 500 * 100 * 2 entries. This sounds like a lot! Is there a more efficient way?

Many thanks in advance for sharing the insight.

(ps. the reason I work with seperate days insetad of a from-date and till-date field, is that the manager can also request the employee for just one or a few days. Then I would either have to split the dates (original-from-date to new-till-date, new-from-date to old-end-date) for each time this happens, or calculate something like this for each employee: from-date to till-date, minus first period (from/till) the employee is "booked", minus the second period the employee is booked etc. This doesn't sound efficient at all)

The problem with the layout you are proposing at the minute is that for each day that they are available you need another entry. What happens if someone is available for 5 days in a row (mon-fri) for both early and late shift? You are mapping their availability and not the actual time they work, so this CAN be the case. Also, do you propose that they have to fill in a day at a time for both shifts for the five days (10 entries total)?
You could encapsulate this all in two entries (a fifth of the size)
How do you envisage this information being entered? How flexible/strict is the entry process.

The numbers of rows you have in your DB should be irrelevant. So long as you index correctly and write decent SQL queries you won't have a problem.

Yes, this could happen, and it would be a valid entry. The manager can then say "I need you Monday and Friday on the early shift". Then I would remove both shifts from the table (because you can't work early AND late) for both Monday and Friday, and copy them to another table (or I add a "taken by manager x" flag to the original table).

So that is indeed how I plan it to work. I'm just not sure if this is the best way, seeing how it results in a huge list of dates when lots of employees enter lots of dates. I'm already scared of the time MySQL will need to find everybody for the month June, for example!

Edit: I see your edit now Please see the ps. in my first post about not choosing to use periods. Because in your example, if the manager would need the employee on just 2007-05-23, I would have to split both the entries into 2007-05-21 (start) - 2007-05-22 (end) and 2007-05-24 (start) to 2007-05-26 (end).

I'm confused by your edited post now :

Are you saying that somehow you are selecting this person for employment from this list. You clearly stated this was an availability table, not an actual employment selection table (i.e. when you are actually employing said person).

So in reality, the developer IS available during those periods and thus you CAN put him in as a perioded date. Why would you split up the availability into those dates and remove the one they are working. Use another table....

If you want a to see who got what working shift you should use a separate table for this.

My apologies for the confusion. Yes, it is indeed an availability table. But when an employee has been picked to work on certain dates, these dates need to be removed from the availability table (according to my logic). But the remaining dates must still be there for another manager to choose from.

Otherwise, when a manager asks "Who is avialable from June to October", I would have to check for each employee what his/hers availability dates are, and check in another table if parts of these dates haven't been "booked" by another manager. This would mean taking the original start- and end date, substracting all "booked" dates from it, and then check if the requested period is still available for this employee. If you do this for 500 employees, I think it will result in a very slow search.

An alternative to this would be to break up the original start- and end date in the actual availability table. I have come to the insight that loose dates is a bad idea, so maybe this alternative is the best way to do this?

(To avoid confusion, I indeed keep the "booked" dates in another table. These should be of no concern to the manager when he looks at the availability table looking for free employees. An oversight of "who is booked when" will be built from this other table)