|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
May 30th, 2004, 02:41 PM
|
|||
|
|||
I got the normalization blues
Hi, I 'm putting together an MS Access database for our employee records and I'm having a real problem designing the schedules table (or tables). I have the following three tables and I know the schedules table is deeply wrong but I can’t figure out how to design it in a good (normalized) way. Any advice would be sincerely appreciated!
(primary keys are listed first) EMPLOYEES TABLE Emp ID Last Name First Name Street Address City State Zip Code ASSIGNMENT TABLE Emp ID Schedule Code (there are 2 start and stop times for each day because some people work split shifts) SCHEDULES TABLE Schedule ID Monday start time 1 Monday stop time 1 Monday start time 2 Monday stop time 2 Tuesday start time 1 Tuesday stop time 1 Tuesday start time 2 Tuesday stop time 2 . . . Friday start time 1 Friday stop time 1 Friday start time 2 Friday stop time 2
|
|
#2
May 30th, 2004, 04:07 PM
|
|||
|
|||
|
normalisation
I'm in a rush at the minute but I'll give you something to play with while I'm gone.
Employee table. Kool Assignment Table. Kool Now: Schedule Table Schedule ID Day ID Start Time Stop Time DayTable Day ID DayName (Monday, Tuesday ...) You could forgo the day table and specify that Sunday = 1 and Monday = 2 etc, but if you have the table it allows you to mess around. Does this work for you or am I just talking out of my hat and I should just go get ready and come back when I have looked at the problem properly?
|
|
#3
May 30th, 2004, 06:33 PM
|
|||
|
|||
|
That helps
Hey Kingkee,
Thanks, that does help  . But it still leaves the problem of split shifts. What do I do when a schedule is Monday 8-12 and 2-6? Some of our people are working that kind of schedule with a few hours off in the middle of the day. How do I handle multiple start and stop times for one day in the schedule table? I appreciate the help, I'm kinda new at this. Bob
|
|
#4
May 30th, 2004, 08:34 PM
|
||||
|
||||
|
I'd do away with the whole Day thing altogether, and just log a start/stop datetime value. It's easy to figure out the Day values when doing your reporting.
|
|
#5
May 30th, 2004, 09:08 PM
|
|||
|
|||
Stumpy is completely right.
The day can be figured out from a datetime stamp. So your schedule table simply has an employee id and a start/stop time. You can order these by datetime and use a sql command like datepart to extract the day or whatever. To make it quicker for reporting on certain days you can use the day thing I was talking about, but it depends on how fast you need it and so on. (removes the need to do a calc on each field when trying to report a certain day) KK
|
|
#7
May 30th, 2004, 11:53 PM
|
||||
|
||||
|
Quote:
|
|
| Viewing: Dev Articles Community Forums > Databases > Database Development > I got the normalization blues |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
