Yes..well my first design is like this.
I have a web interface that I created using php/mysql. It was quite simple and it worked.
the db was designed with three tables emp,avail,area
emp
Code:
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| empid | int(3) | NO | PRI | NULL | |
| empname | varchar(255) | NO | | NULL | |
| availid | int(3) | NO | | NULL | |
| areaid | int(3) | NO | | NULL | |
+---------+--------------+------+-----+---------+-------+
Code:
+-------+---------+---------+--------+
| empid | empname | availid | areaid |
+-------+---------+---------+--------+
| 461 | Justin | 1 | 1 |
| 221 | Jim | 2 | 2 |
| 303 | Sally | 3 | 3 |
| 409 | Chris | 4 | 4 |
| 89 | Phil | 5 | 5 |
| 672 | Dee | 6 | 6 |
| 333 | Rick | 7 | 7 |
| 289 | Tina | 8 | 8 |
+-------+---------+---------+--------+
avail
Code:
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| availid | int(3) | NO | PRI | NULL | |
| availmon | enum('y','n') | NO | | NULL | |
| availtue | enum('y','n') | NO | | NULL | |
| availwed | enum('y','n') | NO | | NULL | |
| availthu | enum('y','n') | NO | | NULL | |
| availfri | enum('y','n') | NO | | NULL | |
| availsat | enum('y','n') | NO | | NULL | |
| availsun | enum('y','n') | NO | | NULL | |
+----------+---------------+------+-----+---------+-------+
Code:
+---------+----------+----------+----------+----------+----------+----------+----------+
| availid | availmon | availtue | availwed | availthu | availfri | availsat | availsun |
+---------+----------+----------+----------+----------+----------+----------+----------+
| 1 | y | y | y | n | y | y | n |
| 2 | n | n | y | y | n | n | y |
| 3 | n | y | y | n | y | n | y |
| 4 | n | n | n | y | y | y | y |
| 5 | y | y | y | y | y | n | n |
| 6 | y | y | n | n | y | y | y |
| 7 | n | n | y | n | n | y | n |
| 8 | n | y | y | y | y | n | y |
+---------+----------+----------+----------+----------+----------+----------+----------+
area
Code:
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| areaid | int(11) | NO | PRI | NULL | |
| areabake | enum('y','n') | NO | | NULL | |
| areadine | enum('y','n') | NO | | NULL | |
| areadish | enum('y','n') | NO | | NULL | |
| arealine | enum('y','n') | NO | | NULL | |
| areareg | enum('y','n') | NO | | NULL | |
+----------+---------------+------+-----+---------+-------+
Code:
+--------+----------+----------+----------+----------+---------+
| areaid | areabake | areadine | areadish | arealine | areareg |
+--------+----------+----------+----------+----------+---------+
| 1 | n | n | y | y | n |
| 2 | y | y | n | y | n |
| 3 | y | y | y | n | y |
| 4 | y | n | n | n | y |
| 5 | y | n | y | y | n |
| 6 | n | n | y | y | y |
| 7 | y | n | n | y | y |
| 8 | n | y | y | n | y |
+--------+----------+----------+----------+----------+---------+
I had an interface that would ask from an drop down option list that was populate with the col names from php/mysql. So you would pick the area you wanted [query is where selected col = y] and day [where selected col=y]...and it would return the emps who were avail based on the options choosen.
I did use the ENUM, so that was one thing i'd like to address. Also there was no certain shifts, which I would like to have since some emps would be at school/busy and not be avail the whole day.
That was my orig design. I'm in the process of going back through and retooling what exactly I'd have it do.
So far....
I'd want to return EMP trained in AREA, on DAY, for SHIFT.
Simple enough... - Justin