Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2011
    Posts
    21

    Rota normalisation for project [homework]

    Hi,

    Ok, I am creating a small application that handles a staff rota and have some data that needs normalising, I am familiar with the process however I've never normalised grid data before, let me explain..

    here are the fields:

    userID (this is included so each staff member can log in independantly, coinciding with other parts of the application)
    Location (this is typically the same)
    Week Beginning
    Week
    Dept (typically the same)
    staffName
    Monday
    Tuesday
    Wednesday
    Friday
    Saturday
    Sunday
    Date
    Hours Owed (calculated, so not to be included)


    The raw data is a simple excel spreadsheet, with the days of the week across the top, the staff names down the side and shifts populating the rest i.e 8-8, 12-4 etc.

    What I need to figure out is the repeating data, the rest will follow

    I spoke with my tutor who reckoned I go with the days of the week as the repeating data, I dont agree. Here is what I reckon to be the repeating data, indented (as the data is repeated over and over):

    userID
    Location
    Week Beginning
    Week
    Dept
    staffName
    Monday
    Tuesday
    Wednesday
    Friday
    Saturday
    Sunday
    Date


    although even that I'm struggling to make sense of, as the repeating group also needs taking to 1NF.

    Any thoughts?

  2. #2
    Join Date
    Apr 2011
    Posts
    21
    lol, i did indent it however obviously not correct here are the fields i think to be repeating:


    userID
    Location
    Week Beginning
    Week
    Dept
    staffName

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you look at the rota data independently of how you want it displayed, then it normalizes quite easily --

    Location
    userID
    assigneddate
    shift

    in other words, you are assigning a shift value (i.e. 8-8, 12-4 etc.) to a particular person at a particular location on a particular date

    that's the repeating data taken care of

    then you'll have other normalizations to do, such as not stuffing the user's name into this table

    displaying the results nicely in "rota" style, one page per location per week, days across the top, staff down the left -- that's the job of the application code
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Apr 2011
    Posts
    21
    thanks for taking a look at it, makes sense now!!

    Your right once you get the fields down its just like any other data, i can get cracking with this now, no problem!

    Yeah that was poor foresight on my part about the 'staffName'!

    thanks so much again!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •