Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007

    Flexible calendar for not available dates

    This database is part of another project.
    My goal is to schedule a commission session dates.
    Meeting cannot have place on some dates , and I need some flexibility while setting these not available dates:
    - Office is always closed on some day of the week (Saturday and Sunday)
    - Office may be closed some other day of the week part of the year (only some months)
    - Office is closed for vacation on some dates per year
    - Some holidays are fixed (Christmas) , some other are movable (Easter)

    I came out with these database schema:
    Click image for larger version. 

Name:	Calendar.png 
Views:	5 
Size:	27.7 KB 
ID:	16907

    Here are some record for Calendar table:
    Click image for larger version. 

Name:	CalendarTable.png 
Views:	1 
Size:	4.9 KB 
ID:	16908

    This is how not available dates are stored (-1 means always):
    Click image for larger version. 

Name:	NotAvailableDates table.png 
Views:	1 
Size:	3.7 KB 
ID:	16909

    New Year’s Day is always on January 1st (-1,1,1,-1)
    This year Easter falls on April 27th (2016,4,27,-1)
    Saturdays and Sundays are always not available (office is closed!!!) (-1,-1,-1,5 and -1,-1,-1,6)
    On june this year, office is closed also on Mondays (2016,6,-1,0)

    What do you think? Other solutions\ideas are welcome.

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    I would build a calendar table with EVERY day. I would include other useful items like whether the office is open or closed, and possibly a comment why (like Christmas, Easter, etc.) This makes the calendar itself data which you can edit and use as any other table. It also means that your code can find EVERY date on the calendar, and any relevant information easily.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2007
    The Calendar table has a row for each day (multiple year)
    Each row in the UnavailableDates table indicates a specific unavailable date (if year, month and day are set) or a repeating unavailable date (i.e. fixed Holiday Year=-1; Month=m; Day=d) .
    The diagram doesn't show a Description column in the table to indicate why the date is unavailable (as you said).

Posting Permissions

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