Re: Design help for Scheduling db.
Posted by: justin bovo
Date: February 12, 2010 09:19PM

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?

Options: ReplyQuote


Subject
Written By
Posted
February 11, 2010 12:27PM
Re: Design help for Scheduling db.
February 12, 2010 09:19PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.