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