Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2008
    Posts
    6

    Unanswered: Database Design for Shift Scheduling:

    I need this done in Access 2007, that is the only pre-requisite.

    Here is the data I wish to store:

    • user identifier (can be username or uniqie # associated with username)
    • days available to work (sunday - saturday)
    • hours available to work each day available to work in hourly increments (sunday 8am - 9am, friday 7pm - 8pm, etc..)
    • dynamic number of users
    • dynamic and varied days/hours of availability


    For the life of me I cannot think of a good schema for this.

    I am currently trying the following:

    • 7 Tables: Sunday, Monday, ... Saturday
    • In each table fields for the following: 8am, 9am, 10am, ... 10pm
    • Storing in each field a comma delimited string of identifiers: user1, user2, ... userN


    This requires extreme processing for just a single user update.

    There has to be a better way. Any ideas?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    one table for the users

    another table for the availability:

    CREATE TABLE availability
    ( userid INTEGER NOT NULL
    , weekday SMALLINT NOT NULL
    , avail_start DECIMAL(5,3) NOT NULL
    , avail_stop DECIMAL(5,3) NOT NULL
    )
    INSERT INTO availability VALUES
    ( 109 , 1 , 8.00 , 11.00 )
    ,( 109 , 2 , 8.00 , 15.00 )
    ,( 103 , 3 , 10.33 , 11.67 )
    ,( 107 , 5 , 8.00 , 17.00 )

    109 is available on sunday from 8 to 11 am, and on monday from 8 to 3 pm

    103 is available only on tuesdays from 10:20 to 11:40 am

    and so on

    this should at least get you started
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2008
    Posts
    6
    This suffers not just from a nightmare of poor efficiency, but also a nightmare of code maintenance.. there has to be a better way.. It also does not meet my requirements of hourly increments (hence, one of the factors leading to horrendous code maintenance).

    Thank you, and I may attempt to adapt this approach, but does anyone have another suggestion?

  4. #4
    Join Date
    May 2007
    Posts
    27
    Actually r937 is bang on with his suggestion for your data structure. The structure you suggested is really messy (separate table for every day), and will give you tons of problems updating / maintaining data. You might look into "normal form" for database design. It gives you rules on how to structure your data.

    r937's structure will allow great flexibility, and coding should not be that hard. You say that the availability needs to be limited to entire hours, and not partial hours? That should be limited at time of data entry, or you could modify the structure of the table to allow only integer entries. Problem solved. All in one table, you only need to change the structure of one table.

    Give r937's structure a go. It's simple, flexible, and will do the job for you.

  5. #5
    Join Date
    Jan 2008
    Posts
    6
    I cannot utilize start / end times. I need the same employee to possibly work 2 different shifts on the same day and this needs to be parsed at the query level.

    This may work.. but I want an easier parsing.

    IE:, I need to break down the start/end times at the query level, not at the scripting language level.
    Last edited by shortaug; 06-25-08 at 16:01.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by shortaug
    I need the same employee to possibly work 2 different shifts on the same day...
    my structure would allow that


    Quote Originally Posted by shortaug
    IE:, I need to break down the start/end times at the query level, not at the scripting language level.
    huh???
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2008
    Posts
    6
    I need to be able to see which hours a person is available to work in hourly increments and this needs to be achievable with SQL. If someone is available from 8am - 11am, I need to see 8am, 9am, 10am and not 8am - 11am.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it sounds like you have really made up your mind about what you need...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2007
    Posts
    27
    Quote Originally Posted by shortaug
    I need to be able to see which hours a person is available to work in hourly increments and this needs to be achievable with SQL. If someone is available from 8am - 11am, I need to see 8am, 9am, 10am and not 8am - 11am.
    Assume the userid is 109, and you want to check is he/she is available for a certain hour (desired_hour), then the following query will do that for you:

    select * from availability where userid=109 and avail_start<=desired_hour and avail_stop>=desired_hour;

    If this query returns a recordcount of 1, then you know you're good. You can make more generic queries by finding everybody who is available for that hour:

    select * from availability where avail_start<=desired_hour and avail_stop>=desired_hour;

    The you can look who has the longest availability within that recordset (a person who is willing to work the most contiguous hours), and start filling in the gaps with other people. The net result will be that you can fill your shift with the least number of people. This is standard database practise, and is not hard to do. Likely though, you will have to combine some creative SQL and basic VBA to accomplish this, but *everything* starts with a good database design. Once the design is good, the code (and SQL) is easy and straightforward.

    Don't take me wrong here, but I think you have a couple of things to learn about database design (or databases in general). Your suggestions, to create a separate record for each hour that a person is available, is much, much harder to maintain that using ranges. Your suggestion to use a table per day is simply wrong.

    There are very smart people on this forum (I am not one of them) who can give very good advise, assuming you are willing to listen to them. If you aren't willing to do so, then you will not learn and improve on your designs, and your database will be a nightmare to work with.

    my 2 cents. Let me know if I can help you with any of the steps.

  10. #10
    Join Date
    Jan 2008
    Posts
    6
    Your breakdown is the explanation I was looking for lola. I have no preference for how the tables must be built, only how I can view that data at the SQL level.

Posting Permissions

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