Re: Design help for Scheduling db.
Hello, thanks for your time.
Your ideas sound similar to what I have tried or plan to try.
With my first go around - I had three tables emp,avail,area.
emp
+---------+--------------+------+-----+---------+-------+
| 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 | |
+---------+--------------+------+-----+---------+-------+
avail
+----------+---------------+------+-----+---------+-------+
| 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 | |
+----------+---------------+------+-----+---------+-------+
area
+----------+---------------+------+-----+---------+-------+
| 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 | |
+----------+---------------+------+-----+---------+-------+
I used ENUM. Some have said that ENUM and SET are no good coding practice since they are not portable and are native to mySQL.
I will be using SELECT statements.
With the current configuration I have it set up as a web interface with two drop down options. User would select avail [mon-sun] and area [bake-reg]. These variables would be passed via post method and sent to a query ex[$query = "select empname from emp join area a using (areaid) join avail b using (availid) where a.$area = 'y'and b.$avail = 'y'";] and this would return all emp that were avail on the selected day and the selected area...
but now I would like to refine. For example, even though an emp could work on a Tues they might not be avail until after a certain time ex[school, other jobs, family].
So I have broken them into shifts like [Open,AM,PM,Close,Any].
...and that is where I'm at now.
I then thought of using BOOL vals..
Another thing I thought of but doesn't seem realistic in implementation was to use BOOL values, like so....
tblDay
-------
dayID 01 20 30 40 50 60 70
dayDesc Mon Tue Wed Thu Fri Sat Sun
tblShift
--------
shiftID 10 12 13 14 15 16
shiftDesc Open AM PM Close Any None
tblArea
-------
areaID 21 22 23 24 25
areaDesc Bake Dine Dish Line Register
tblEmpDayShift
--------------
empID 209
dayDesc Tue
shiftDesc Any
areaDesc Dine
DAY-AREA-SHIFT
return EMP trained in AREA, on DAY, for SHIFT.
tblArea2
---------
area2ID 31 32
empID 209 244
areaBake T F <-----|
areaDine F T |
areaDish F T |----Bool ?
areaLine T T |
areaReg F T <-----|
--------------------------------------
Now that would seem right for Areas, but when I look to Day it is similar...
tblDay2
---------------------------
day2ID 41 42
area2ID 31 32
day2Mon T F
day2Tue F F
day2Wed F T
day2Thu F T
day2Fri T T
day2Sat T F
day2Sun T T
Now the problem arises when their are different avails for each day, so I can not use this method for Shifts. This led me to tweaking tblDay2 like so....
tblDay2
---------------------------
day2ID 41 42
area2ID 31 32
day2Mon T F
---------------------------
tblday2MonShift
------------------
day2ID 41
monShiftOpen T
monShiftAM T
monShiftPM T
monShiftClose T
monShiftAny T
------------------
Then in essence I would need tblday2Mon-SunShift..?
I feel like I am over complicating this ?
Thanks - Justin
PS- sorry for the ugly formatting, is there a way to make my data look better?