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.
February 11, 2010 12:27PM


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.