Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2011
    Posts
    8

    need help with design

    hello there i am building a application to schedule people for different times and places. They can be scheduled based on qualifications and availability. i have figured the qualification part out but i am stuck on the amiability part

    should use a table with an ID field and 366(for leap year) fields or is there a simpler way to do this i am so confused brain hurts

    thanks for the help in advance

  2. #2
    Join Date
    Dec 2011
    Posts
    8
    the current data structure

    ROSTER
    ===========
    ID
    NAME
    DEPARTMENT

    QUALS
    ===========
    ID
    SUN1
    SUN2
    SUN3
    SUN4
    MON1
    MON2
    MON3
    MON4
    TUE1
    TUE2
    TUE3
    TUE4
    WEN1
    WEN2
    WEN3
    WEN4
    THU1
    THU2
    THU3
    THU4
    FRI1
    FRI2
    FRI3
    FRI4
    SAT1
    SAT2
    SAT3
    SAT4

    THE TABLE I NEED HELP WITH IS THE UNAVAIL TABLE WHERE I WANT TO STORE THE DATE IN WHICH A PERSON IS UNAVAILABLE TO BE AND NOT CONSIDERED FOR ASSIGNMENT

    I HOPE THAT CLARIFIES THINGS BETTER

  3. #3
    Join Date
    Feb 2012
    Posts
    76
    In my rostering system, I've got something like:

    Teachers (Teacher -> Name:str)
    Rooms (Room -> Name:str)
    Timetables (Timetable -> Year:int)
    Days (Day -> Timetable, Label:str, Periods:int)
    Periods (Period -> Day, Label:str)
    Bookings (Booking -> Period, Teacher, Room)
    Unavailability (Teacher, Period)

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    as soon as I se a table with columns named such as Mon1, Mon2, Mon3 etc I immediately start to think the table design is suspect and not normalised.
    Last edited by healdem; 03-23-12 at 11:29.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2010
    Location
    Fort Lauderdale, FL
    Posts
    5
    Quote Originally Posted by fluidmedia View Post
    the current data structure...
    ...THE TABLE I NEED HELP WITH IS THE UNAVAIL TABLE WHERE I WANT TO STORE THE DATE IN WHICH A PERSON IS UNAVAILABLE TO BE AND NOT CONSIDERED FOR ASSIGNMENT

    I HOPE THAT CLARIFIES THINGS BETTER
    I think your modeling problems run deeper than that.

    Would you mind in showing us your E/R Model?

  6. #6
    Join Date
    Dec 2011
    Posts
    8
    Quote Originally Posted by reaanb View Post
    in my rostering system, i've got something like:

    Teachers (teacher -> name:str)
    rooms (room -> name:str)
    timetables (timetable -> year:int)
    days (day -> timetable, label:str, periods:int)
    periods (period -> day, label:str)
    bookings (booking -> period, teacher, room)
    unavailability (teacher, period)
    how does your unavailability work

    Quote Originally Posted by healdem View Post
    as soon as i se a table with columns named such as mon1, mon2, mon3 etc i immediately start to think the table design is suspect and not normalised.
    it probably isnt

    Quote Originally Posted by paulb_ View Post
    i think your modeling problems run deeper than that.

    Would you mind in showing us your e/r model?
    i dont even know what that is

  7. #7
    Join Date
    Feb 2012
    Posts
    76
    Quote Originally Posted by fluidmedia View Post
    how does your unavailability work
    I thought the relations I posted described it quite succinctly. In English then: my timetables consist of a repeating cycle of days which are broken into a number of time periods. A (teacher, period) tuple in the unavailability table simply indicates that the specified teacher is unavailable in the specified period.

  8. #8
    Join Date
    Jun 2010
    Location
    Fort Lauderdale, FL
    Posts
    5
    Quote Originally Posted by fluidmedia View Post
    how does your unavailability work



    it probably isnt



    i dont even know what that is
    E/R Model stands for Entity/Relationship Model - if you are designing a database I would suggest to search the internet for "E/R Model" and start there otherwise is like trying to train a toddler to run the 100 meters in the olympics when the toddler is not able to walk yet. :-)

    Basically, E/R Model will help you to identify the different "entities" in your system as well as the "relationships" between them; this is paramount to move to the Physical Model phase when you will design your tables, etc.

    Hope this helps.

  9. #9
    Join Date
    Dec 2011
    Posts
    8
    Quote Originally Posted by reaanb View Post
    I thought the relations I posted described it quite succinctly. In English then: my timetables consist of a repeating cycle of days which are broken into a number of time periods. A (teacher, period) tuple in the unavailability table simply indicates that the specified teacher is unavailable in the specified period.
    i think im getting it but my brain will not let me see it i need a visual i think i will work on the design again maybe start from scratch

    my unclear vision may be due to the fact that im think in database and programming at the same time its hard to turn the programming off LOL

  10. #10
    Join Date
    Dec 2011
    Posts
    8
    Quote Originally Posted by PaulB_ View Post
    E/R Model stands for Entity/Relationship Model - if you are designing a database I would suggest to search the internet for "E/R Model" and start there otherwise is like trying to train a toddler to run the 100 meters in the olympics when the toddler is not able to walk yet. :-)

    Basically, E/R Model will help you to identify the different "entities" in your system as well as the "relationships" between them; this is paramount to move to the Physical Model phase when you will design your tables, etc.

    Hope this helps.
    i have went to google and have found a good understanding of what it is i have downloaded and installed mysql workbench and will use this to help me understand and visualize the design thanks

  11. #11
    Join Date
    Dec 2011
    Posts
    8
    thanks you to everyone who has replied to this thread

    ok here is what i am trying to (keep in mind that i have very little experience with data base design)

    i have a group of people that i want to assign to 2 postion on 3 shifts everyday of the week everyday of the year

    the rules are
    to stand the position
    1. you must me qualified to do so
    2 you must be available for that day
    3. you must be available for the required shift

    so i start out with table like such

    roster
    *****************
    #rosterid
    name

    day
    ************
    #dayid
    rosterid
    sunday
    monday
    tuesday
    wednesday
    thursday
    friday
    saturday

    shifts
    **********
    #shiftid
    rosterid
    first
    secound
    third

    position
    **********
    #positionid
    rosterid
    ddo
    dd
    bi
    ok i think im stuck i cant see the relationships this way

  12. #12
    Join Date
    Dec 2011
    Posts
    8
    this is the form im trying to fill out

    Sunday
    ADDO | DD
    =============================
    first = Joe | Pam |
    =============================
    Second = John | George |
    =============================
    Third = Michael | Sally |
    =============================

  13. #13
    Join Date
    Feb 2012
    Posts
    76
    i have a group of people that i want to assign to 2 postion on 3 shifts everyday of the week everyday of the year
    It sounds like you want a table with columns (Date, Shift, Position, Employee) where:

    - Date is a date, but Date is often a reserved word, so you may want to pick something else.
    - Shift is 1, 2 or 3
    - Position is 1 or 2
    - Primary key is (Date, Shift, Position)
    - Employee is a FK to your Employees table.

    Don't overcomplicate it, and don't put your data in the table design. If your dbms supports it, add check constraints to enforce the shift and position domains rather than just using plain integers.

    Unavailability can be handled very similarly, see if you can work it out.

    You don't describe how qualifications should work. It could be as simple as QualifiedEmployees (Employee PK FK), and let the foreign key in the table above reference this.
    Last edited by reaanb; 03-25-12 at 05:01. Reason: Unavailability and qualifications

  14. #14
    Join Date
    Dec 2011
    Posts
    8
    ok i think i am re thinking the wheel and have gotten off track.

    my original table design takes the seven days in a week and combines the three shifts and comes up with 21 different availability options per position

    im ok with that

    my original question my not have been clear
    i wanted help with the table that would keep track of when employees went on vacation or where assign out side of the department for periods of time

    i think i will just have a table with 5 columns for possible unavailability periods and run a script to clean this table up looking for past dates and removing them

    thanks to everyone that helped me but i think i will need a instructed course on database to get any deeper than i am know


Posting Permissions

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