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
+-------+---------+---------+--------+
| 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
Subject
Written By
Posted
Design help for Scheduling db.
February 11, 2010 12:27PM
February 12, 2010 08:23PM
February 12, 2010 09:19PM
February 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.