PHP model (MySQL) design problem

I'm looking for the most efficient solution to the problem I'm running into. I'm designing a shift calendar for our employees. This is the table I'm working with so far:

CREATE TABLE IF NOT EXISTS `Shift` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`accountId` smallint(6) unsigned NOT NULL,
`grpId` smallint(6) unsigned NOT NULL,
`locationId` smallint(6) unsigned NOT NULL,
`unitId` smallint(6) unsigned NOT NULL,
`shiftTypeId` smallint(6) unsigned NOT NULL,
`startDate` date NOT NULL,
`endDate` date NOT NULL,
`needFlt` bit(1) NOT NULL DEFAULT b'1',
`needBillet` bit(1) NOT NULL DEFAULT b'1',
`fltArr` varchar(10) NOT NULL,
`fltDep` varchar(10) NOT NULL,
`fltArrMade` bit(1) NOT NULL DEFAULT b'0',
`fltDepMade` bit(1) NOT NULL DEFAULT b'0',
`billetArrMade` bit(1) NOT NULL DEFAULT b'0',
`billetDepMade` bit(1) NOT NULL DEFAULT b'0',
`FacilityId开发者_开发技巧` smallint(6) unsigned NOT NULL,
`FacilityWingId` mediumint(9) unsigned NOT NULL,
`FacilityRoomId` int(11) unsigned NOT NULL,
`comment` varchar(255) NOT NULL,
`creation` datetime NOT NULL,
`lastUpdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`lastUpdateBy` mediumint(9) unsigned NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Now here's the hitch - I'd like to be able to display on the calendar (in a different color) whether or not a timesheet has been received for a certain day.

My first thought was to create a separate table and list separate entries by day for each employee, T/F. But the amount of data returned from a separate query, for each employee, for the whole month would surely be huge and inefficient.

Second thought was to somehow put the information in this Shift table, with delimiters - then exploding it with PHP. Silly idea... but I guess that's why im here. Any thoughts?

Thanks for your help!

---

As hinted previously and I think you realized yourself, serializing the data into a single column or using some other form of delimited string is a path to computational inefficiencies in the packing and unpacking and serious maintenance grief for the future.

Heaps better is to get the data structure right, i.e. a properly normalized table. After all, MySQL is rather good at dealing with this some of structure.

You don't need to pull back every line for every staff member. If you're pull them out together, you could "group" your resultset by employee and date, and even make that a potentially useful result by (say) pulling the summary of hours. A zero result or null result would show no timesheet, and the total hours may be helpful in some other way.

If you were pulling them out an employee and a date at a time then your application structure probably needs looking at, but you could use the SQL LIMIT keyword to pull at most one record and then test to see if any came back.