Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Design help for Scheduling db.

    Hello friends,

    My question is concerning the design of my db.
    I'm a creating a scheduling web application that will assist in the creation of a schedule for a local restaurant.

    I have this broken into three tables [emp, area, avail].

    The emp tbl has four cols [id, name, areaid, availid]
    The avail tbl has eight cols [availid, availm-availsu(Monday through Sunday)]
    The area tbl has six cols [areaid, areabak-areareg (the five areas where emps can be staffed)]

    1. In the emp tbl I have emp.areaid-->area.areaid and emp.availid--->avail.availid. emp.areadid and emp.availid are the same.
    ex
    Code:
    +-------+---------+---------+--------+
    | empid | empname | availid | areaid |
    +-------+---------+---------+--------+
    | 221 | Jim | 2 | 2 |
    How can I eliminate the redundancy of these two columns.

    2. In the avail tbl I have the avail days set up as ENUM, either Y-N.
    ex.
    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 |
    Is there a better way to store these availabilities?

    3. In the area table , I may have been ENUM-happy when designing and done they same with areas emp's are trained to work in.
    ex.
    Code:
    +--------+----------+----------+----------+----------+---------+
    | areaid | areabake | areadine | areadish | arealine | areareg |
    +--------+----------+----------+----------+----------+---------+
    | 1 | n | n | y | y | n |
    | 2 | y | y | n | y | n |
    Again, is there a better way to store areas an emp is trained in?

    Thanks for any help and input - this would be greatly appreciated.
    -Justin

  2. #2
    Join Date
    Jul 2009
    Location
    Michigan
    Posts
    125
    Create a table to define times people can work. This would have attributes like DayShiftID, Day, Shift and would contain data like 1, Monday, AM; 2, Monday, PM; 3, Monday, Evening.

    Create a table to define areas people could work. This would have attributes like AreaID, Description and would contain data like 1, Bakery; 2, Dining Room; 3, Dishwasher.

    Create an employee table with attributes like PersonID, Name.

    Now create a table that links people to when they can work. It would have attributes of PersonID and DayshiftID.

    Now create a table that links people to what areas they work in. It would have attributes of PersonID and AreaID.

  3. #3
    Join Date
    Feb 2010
    Posts
    11
    Hello,

    Well, I think I have fixed my first problem, but you may have answered my next question.

    I re-worked my db like so...

    I created an emp tbl that has the cols : id, fname, lname, avail, area.
    I used datatype SET for avail [Mon-Sun]
    I used datatype SET for area [Bake-Register]

    ex.
    Code:
    +-------+--------------+-------------+--------------------------------------------+--------------------+
    | empID | empFirstName | empLastName | empAvail                                   | empArea            |
    +-------+--------------+-------------+--------------------------------------------+--------------------+
    |   192 | Carly        | Smith       | Monday,Wednesday,Friday,Sunday             | Bakery,Dine,Dish   | 
    |   209 | Chris        | Jones       | Tuesday,Thursday,Sunday                    | Dish,Line,Register |
    ...but then you are correct in noting that an emp maybe not be avail all day. ex- school, other jobs, family.

    I will implement you design idea and see how it works!

    Thanks for your time and ideas!
    -Justin

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    FYI you will want to stay well clear of proprietary datatypes like ENUM and SET, both of which are evil (my opinion) and both of which are not portable to other database systems (fact)

    in other words, don't learn bad habits that will only create errors if/when you should ever wish to work on any other database system
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2010
    Posts
    11
    I understand where you are coming from..
    One of the least commonly used of the MySQL datatypes has to be the SET datatype. The SET datatype is a string type, but is often referred to as a complex type due to the increased complexity involved in implementing them.
    ...per the mySQL docs.

    Now I have tried the implementation of the idea and it goes as follows...

    I have tblarea, tblemp, tblshift.

    Like mentioned...
    tblarea contains cols areaID, areaDesc
    tblemp contains cols empID,empFname,empLname
    tblshift contains cols shiftID,day,shifts

    I find that tblshift has now become redundant in its data...(a small piece)
    Code:
    |      17 | Friday    | Open   | 
    |      18 | Friday    | AM     | 
    |      19 | Friday    | PM     | 
    |      20 | Friday    | Close  | 
    |      21 | Saturday  | Open   | 
    |      22 | Saturday  | AM     | 
    |      23 | Saturday  | PM     | 
    |      24 | Saturday  | Close  | 
    |      25 | Sunday    | Open   | 
    |      26 | Sunday    | AM     | 
    |      27 | Sunday    | PM     | 
    |      28 | Sunday    | Close  |
    ...thats one thing.

    Another is that my db is MyISAM - so there is no use of FK.
    I have created tblempShifts that has cols empID, shiftID ( I really want emp.empID and shift.shiftID..right?)...this again brings me back to being repetitive....
    Code:
    +-------+---------+
    | empID | shiftID |
    +-------+---------+
    |   209 |       2 | 
    |   209 |       5 | 
    +-------+---------+
    ...with the more availability an emp has, the increase in entries in this table.

    I could include an Mon-Sun [ALLDAY] shift...that's all I could think of.

    Ok thanks again, I will continue to Iron it out. - Justin

  6. #6
    Join Date
    Feb 2010
    Posts
    11
    Another thing I thought of but doesn't seem realistic in implementation was to use BOOL values, like so....

    Code:
    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...

    Code:
    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....

    Code:
    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 going in circles -- Am I over complicating this ?

    Thanks - Justin

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by justintoo1 View Post
    Am I over complicating this ?
    not at all

    you are brainstorming possible solutions



    my advice is to figure out first why you need to store this stuff, what information retrieval it will be used for

    good database design begins with system analysis
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2010
    Posts
    11
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •