display on the calendar (in a different color) whether or not a timesheet has been received for a certain day

Question

I'm designing a shift calendar for our employees.

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 ;

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.

I could 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 be huge and inefficient.

I could put the information in this Shift table, with delimiters - then exploding it with PHP. Silly idea.

Answer 1

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.