Skip navigation links

MySQL Forums :: Database Design & Data Modelling :: Design help for Scheduling db.


Advanced Search

Design help for Scheduling db.
Posted by: justin bovo ()
Date: February 11, 2010 12:27PM

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
+-------+---------+---------+--------+
| 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.
+---------+----------+----------+----------+----------+----------+----------+----------+
| 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.
+--------+----------+----------+----------+----------+---------+
| 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

Options: ReplyQuote


Subject Written By Posted
Design help for Scheduling db. justin bovo 02/11/2010 12:27PM
Re: Design help for Scheduling db. Rick James 02/12/2010 08:23PM
Re: Design help for Scheduling db. justin bovo 02/12/2010 09:19PM
Re: Design help for Scheduling db. Rick James 02/13/2010 01:12AM


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.