Table Design Question

[Joseph Hesse]

I want to keep track of the days employees are able to work. For
example, John may only be able to work on Sun, Fri, and Sat and not work
on the other days.

The following is a fragment of a table I created to record this
information. I'm wondering if anyone else had a similar problem with a
more elegant solution.

Thank you,
Joe

CREATE TABLE Schedule (
EmployeeID INT NOT NULL ,
AvailableSun TINYINT(1) NOT NULL ,
AvailableMon TINYINT(1) NOT NULL ,
AvailableTue TINYINT(1) NOT NULL ,
AvailableWed TINYINT(1) NOT NULL ,
AvailableThu TINYINT(1) NOT NULL ,
AvailableFri TINYINT(1) NOT NULL ,
AvailableSat TINYINT(1) NOT NULL ,
PRIMARY KEY (EmployeeID) );

[Erick T. Barkhuis]

Joseph Hesse:

>I want to keep track of the days employees are able to work. For
>example, John may only be able to work on Sun, Fri, and Sat and not
>work on the other days.
>
>The following is a fragment of a table I created to record this
>information. I'm wondering if anyone else had a similar problem with
>a more elegant solution.

>

>CREATE TABLE Schedule (
> EmployeeID INT NOT NULL ,
> AvailableSun TINYINT(1) NOT NULL ,
> AvailableMon TINYINT(1) NOT NULL ,
> AvailableTue TINYINT(1) NOT NULL ,
> AvailableWed TINYINT(1) NOT NULL ,
> AvailableThu TINYINT(1) NOT NULL ,
> AvailableFri TINYINT(1) NOT NULL ,
> AvailableSat TINYINT(1) NOT NULL ,
> PRIMARY KEY (EmployeeID) );

If you described all requirements, and you don't need stuff like
- "When does he plan days off and/or holiday"
- "Well he work on a next day if his working day is a national holiday"
- "What did his availability look like a year ago"
- "His availability will change next month...I want to enter
those changes now already, but the old situation must still
be there for this month's planning cycle."

then this seems fine. There's no need to normalize things any further,
since there are no repeating attributes in your entity. Also, it's not
likely that the structure of a calender week will ever change.

Why do you consider your table not elegant enough?

--
Erick

[Captain Paralytic]

No problem with that as a model for what you described. When I had to
solve a similar problem, that one recognised that it was actually
dates that people could/could not work, rather than the same days
repeating every week.

[Luuk]

CREATE TABLE `schedule` (
`EmployeeID` int(11) NOT NULL,
`DOW` enum('1','2','3','4','5','6','7') NOT NULL,
`Avalable` tinyint(1) NOT NULL,
PRIMARY KEY (`EmployeeID`,`DOW`)
);

Is not more elegant,
but might be more usefull
if you need something like i.e.:
Give /me everyone who's available on Tuesdays and Thursdays.

When you have 'a lot' of Employee's with your table a query will never
use a proper index.....

[Erick T. Barkhuis]

Luuk:

>On 10-05-2012 07:55, Erick T. Barkhuis wrote:
>> Joseph Hesse:
>>
>>> I want to keep track of the days employees are able to work.
>>>

>>> CREATE TABLE Schedule (
>>> EmployeeID INT NOT NULL ,
>>> AvailableSun TINYINT(1) NOT NULL ,
>>> AvailableMon TINYINT(1) NOT NULL ,
>>> AvailableTue TINYINT(1) NOT NULL ,
>>> AvailableWed TINYINT(1) NOT NULL ,
>>> AvailableThu TINYINT(1) NOT NULL ,
>>> AvailableFri TINYINT(1) NOT NULL ,
>>> AvailableSat TINYINT(1) NOT NULL ,
>>> PRIMARY KEY (EmployeeID) );
>>

>> If ...
>> then this seems fine.

>> Why do you consider your table not elegant enough?
>>
>
>CREATE TABLE `schedule` (
> `EmployeeID` int(11) NOT NULL,
> `DOW` enum('1','2','3','4','5','6','7') NOT NULL,
> `Avalable` tinyint(1) NOT NULL,
> PRIMARY KEY (`EmployeeID`,`DOW`)
>);
>
>Is not more elegant,
>but might be more usefull
>if you need something like i.e.:
>Give /me everyone who's available on Tuesdays and Thursdays.

I may be missing something, Luuk.

With the former table, that would be
SELECT EmployeeID FROM schedule
WHERE AvailableTue = 1 AND AvailableThu = 1

How would that be with your table? Would you use a self join of some
sort to find employees working on Tuesday and Thursday?

>When you have 'a lot' of Employee's with your table a query will never
>use a proper index.....

An organization with more than, say, 5000 employees will probable not
use a MySql table like this to administer employee availability. :-)


--
Erick

[Luuk]

select s1.EmployeeID
from schedule s1
join schedule s2
on s2.DOW=4 and s2.Avalable=1
where s1.DOW=2 and s1.Avalable=1;

hmm, typo in the fieldname 'Avalable' ;(

>> When you have 'a lot' of Employee's with your table a query will never
>> use a proper index.....
>
> An organization with more than, say, 5000 employees will probable not
> use a MySql table like this to administer employee availability. :-)
>
>

True ;)

[Jerry Stuckle]

What would they use then? An Excel spreadsheet?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================

[Erick T. Barkhuis]

Jerry Stuckle:

>On 5/12/2012 3:18 AM, Erick T. Barkhuis wrote:
>>Luuk:

>>>When you have 'a lot' of Employee's with your table a query will
>>>never use a proper index.....
>>
>>An organization with more than, say, 5000 employees will probable
>>not use a MySql table like this to administer employee
>>availability. :-)
>
>What would they use then? An Excel spreadsheet?

No, of course not. These big companies listen to you and use a real
database, like Oracle. Then, they keep track of 'employee availability'
in terms of day-of-week, only.

--
Erick

[Thomas 'PointedEars' Lahn]

Joseph Hesse wrote:

> I want to keep track of the days employees are able to work. For
> example, John may only be able to work on Sun, Fri, and Sat and not work
> on the other days.
>
> The following is a fragment of a table I created to record this
> information. I'm wondering if anyone else had a similar problem with a
> more elegant solution.

> […]

>
> CREATE TABLE Schedule (
> EmployeeID INT NOT NULL ,
> AvailableSun TINYINT(1) NOT NULL ,
> AvailableMon TINYINT(1) NOT NULL ,
> AvailableTue TINYINT(1) NOT NULL ,
> AvailableWed TINYINT(1) NOT NULL ,
> AvailableThu TINYINT(1) NOT NULL ,
> AvailableFri TINYINT(1) NOT NULL ,
> AvailableSat TINYINT(1) NOT NULL ,
> PRIMARY KEY (EmployeeID) );

You have not told your MySQL versions (client and server), so I am assuming
the currently newest version of the oldest minor version that is still
supported, 5.1.64 [0]. Also, the context of your use case is unknown.

Therefore, I can only suggest

CREATE TABLE `schedule` (
`employee_id` INT UNSIGNED NOT NULL,
`sun` BOOLEAN NOT NULL,
`mon` BOOLEAN NOT NULL,
`tue` BOOLEAN NOT NULL,
`wed` BOOLEAN NOT NULL,
`thu` BOOLEAN NOT NULL,
`fri` BOOLEAN NOT NULL,
`sat` BOOLEAN NOT NULL,
CONSTRAINT `employee_id` UNIQUE KEY `employee_id` (`employee_id`)
);

Tested positive with MySQL server/client 5.1.62(-1-log) on Debian GNU/Linux.

Explanation:

I keep my table and column names lowercase so that I do not have to worry
about letter case; that may also compress a bit better. I try to always
quote names so that the query still works if they become reserved words
later.

I prefer my keys to be non-negative integers¹, therefore UNSIGNED.

If you have a smaller company or little employee rotation, the 4-byte-INT
might be overkill (4'294'967'296 possible IDs, after all, ISTM one less with
AUTO_INCREMENT), so perhaps MEDIUMINT or SMALLINT suffices. [1] On the
other hand, the 4-byte value might be faster with 32-bit (4-byte) CPUs (but
64-bit CPUs are common in servers now). Of course, if you want to use
`employee_id' as reference for an InnoDB foreign key (as the column name
suggests, see below), the data types must match. [2]

I know that e. g. MySQL Workbench generates TINYINT(1) if you select
BOOLEAN² (as they are equivalent; and your query sure looks generated, with
all the spaced commas), but I like to be explicit about my queries. There
is also BIT(1), which should save more space (TINYINT(1) still reserves
space for 256 different values) [3] (I did not know that), but it might not
be as easy to handle.

That `employee_id' should be a primary key here does not make sense to me.
Surely you want to avoid storing multiple schedules for the same employee,
but it is not this table which should define the employee ID but the
`employee` table. I suggest having the latter table with that name and
`employee_id` as primary key, and a UNIQUE key on this table's `employee_id`
column instead. The constraint is given a symbol identical to the column
name so that it can be easily modified later. [4]

If you do not want to have another `employee` table – but I think if you do
not have it yet you will be needing it soon, see below –, keep the PRIMARY
KEY in this table, and name this table `employee` instead of `schedule` (you
would be storing other per-employee information like name and phone number
in this table, then).

The names of primary key columns should include the (abbreviation of the)
name of the corresponding table; that makes it easier to understand a
database's structure and writing cross-table queries (`… JOIN … USING (…)').
I have found the `{$tablename}_id' format, where the column name consists of
at most 11 US-ASCII characters, to be the most compatible and best
maintainable.

In any case, for one-column integer PRIMARY KEYs, adding AUTO_INCREMENT is a
good idea. [4]

Other than that, no considerable improvements to this table's structure come
to my mind now. But it all depends what information you want to get into
and out of this table, i. e. what queries should be possible and how often
they will be made.

You might also find yourself soon in a situation where the workdays of
employees are no longer merely defined by weekdays, and then the next
logical step would be to split employee meta-information and workdays table
in two tables. So you might as well do that right now.

HTH

________
¹ I have encountered only one database so far that uses non-numeric
(VARCHAR!) primary keys: that of OXID eShop. As the keys are calculated,
it is impossible to quickly add rows to a table that conform to that
naming scheme; collisions are likely. No, the generator is _not_ a stored
procedure; it is coded in PHP (and you are lucky if you find it in the
unscrambled part). I think I do not want to know why that is and try to
avoid the software in favor of better designed ones which also use PHP and
a MySQL database (like Magento) instead.
² I find that implicit conversion in MWB extremely confusing and detrimental
to import/export/ALTER script creation; why don't they let the database
engine do the conversion instead?

[0] <http://www.oracle.com/us/support/lifetime-support/index.html>
[1] <http://dev.mysql.com/doc/refman/5.1/en/integer-types.html>
[2] <http://dev.mysql.com/doc/refman/5.1/en/
innodb-foreign-key-constraints.html>
[3] <http://dev.mysql.com/doc/refman/5.1/en/numeric-type-attributes.html>
[4] <http://dev.mysql.com/doc/refman/5.1/en/create-table.html>
--
PointedEars

Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[Jerry Stuckle]

Some of them do, but just because they're big doesn't mean they don't
use MySQL.

[Erick T. Barkhuis]

Jerry Stuckle:

>On 5/12/2012 12:20 PM, Erick T. Barkhuis wrote:
>>Jerry Stuckle:
>>
>>>On 5/12/2012 3:18 AM, Erick T. Barkhuis wrote:
>>>>Luuk:
>>
>>>>>When you have 'a lot' of Employee's with your table a query
>>>>>will never use a proper index.....
>>>>
>>>>An organization with more than, say, 5000 employees will
>>>>probable not use a MySql table like this to administer employee
>>>>availability. :-)
>>>
>>>What would they use then? An Excel spreadsheet?
>>
>>No, of course not. These big companies listen to you and use a real
>>database, like Oracle. Then, they keep track of 'employee
>>availability' in terms of day-of-week, only.
>>
>
>Some of them do, but just because they're big doesn't mean they don't
>use MySQL.

Jerry, my remark wasn't so much about the DBMS, but rather about the
fact that such businesses will not merely keep track of employee
availability by day-of-week only.

These companies probably have advance time tracking and scheduling
software in place. They will, if necessary, keep track of various
availability patterns ("daily 8am-noon", "mon-wed every second week",
"anytime, but never more than 20 hours/week", etc.). Such companies
will not have a table like the one suggested in this thread.

Tracking back, this was just a remark to Luuk, who wrote:
>When you have 'a lot' of Employee's with your table a query
>will never use a proper index.....

Then I responded (see above). Luuk understood immediately.

You missed the point.


--
Erick

[Jerry Stuckle]

And what are you placing your "probably" on? Your feeling? Or do you
have some proof of this?

>
>
> Tracking back, this was just a remark to Luuk, who wrote:
>> When you have 'a lot' of Employee's with your table a query
>> will never use a proper index.....
>
> Then I responded (see above). Luuk understood immediately.
>
> You missed the point.
>
>

I didn't miss the point at all. But your point is indefensible.

[Denis McMahon]

On Sat, 12 May 2012 07:18:59 +0000, Erick T. Barkhuis wrote:

> An organization with more than, say, 5000 employees will probable not
> use a MySql table like this to administer employee availability. :-)

I can't imagine any sort of organization wanting to track that many
employees availability in one place.

Perhaps at the departmental or section level such data is needed to
ensure a minimum level of cover is required, and maybe at the corporate
level such data is needed for senior managers, but I think this would
generally be a departmental activity rather than a corporate one.

Mind you, I'm so old fashioned if I was tracking availability on a day of
week basis I'd be tempted to use a single char and treat it as a bitfield.

Rgds

Denis McMahon