Results 1 to 7 of 7
  1. #1
    Join Date
    May 2015
    Posts
    3

    Scheduling Database Design

    Good afternoon,

    I am a novice when it comes to database programming, but I would like to implement my idea (that I'd like your input on), as a way to learn SQL databases.

    Background:
    In my line of work, we have a small number of employees who visit customer sites, and at the moment, we schedule their work using an excel spreadsheet that is sent out by email to the employees each week. The spreadsheet is in the format of:
    Click image for larger version. 

Name:	schedule layout.gif 
Views:	5 
Size:	23.4 KB 
ID:	16374
    This works fine, but there are a number of annoyances with this method.
    Firstly, any changes to the schedule after it is sent, are not visible to the employees when on the road.
    Secondly, if an employee changes their schedule, they cannot update the spreadsheet whilst on the road, and have to 'phone in' changes they have made.

    The Idea:
    My idea is to create an SQL database to store the employee schedule information and a web front end to display it online.
    From my initial research into this, I believe I need to use SQL tables to include the following information:

    • Employee Name
    • Date
    • Day of the Week
    • Morning Customer Name
    • Morning Work Type
    • Afternoon Customer Name
    • Afternoon Work Type
    • Any additional notes


    I'm sure that there would be other useful data that could be included, such as holiday allowance etc. but this might be something I could incorporate at a later date, unless creating all fields at the beginning be a better way to do it?

    I am quite happy programming a web front end, and reasonably happy with C#.net to populate the front end (once I have figured out how to put data into and out of the SQL database).


    Any input you can give would be greatly appreciated.

    Oliver

  2. #2
    Join Date
    Jun 2015
    Location
    Boston, MA
    Posts
    16
    First of all, if you can give employees access to a database on line, you can give them access to a spreadsheet on line. Using a cloud-storage service would be a good, simple, quick route to sharing a spreadsheet.

    If you want to build a database, I would start with these tables:

    Employees
    Customers
    Tasks
    ScheduledWork

    The ScheduledWork table would bring the other three tables together. Each record (row) would be a scheduled task (employee, customer, task, date-and-time).

    - Bruce Hyatt

  3. #3
    Join Date
    Feb 2012
    Posts
    76

    Thumbs up

    Your spreadsheet already implements a data model. Extracting it is easy enough if you know how to read it. Since this is a simple example, let's analyze it properly as a tutorial.

    Essentially, we have a customer and work type allocated per employee per date per shift, if the date isn't a weekend day or holiday.

    I underlined the nouns, these are our initial domains. The schedule is basically a mapping:
    Code:
    (employee, date, shift) -> (customer, work type)
    meaning that for any combination of employee, date and shift, we can record a customer and work type.

    Now, let's look at each of the domains, and ask how we'll identify it, and what related info we need.

    For customers, you indicated that you want to record a name. It's possible that different customers might have the same name, so that's not a good basis for a formal identity. People and organizations are commonly identified by surrogate numbers (like the employee numbers in your spreadsheet) since natural or social attributes are frequently unreliable, even in combination. I'm not going to indicate that now, though, I'll just continue using domains.
    Code:
    (customer) -> (name)
    The work types on your spreadsheet are:
    Code:
    yellow -> installation
    green -> planned maintenance
    red -> breakdown
    gray -> admin/office day
    So if we generalize, that means:
    Code:
    (color) -> (work_description)
    Color might not be the best identifier, since you might want to change the assigned colors later or use the same color for related tasks. We can give each work type a surrogate number or code instead:
    Code:
    (work_type) -> (color, work_description)
    Employees work very much the same as customers:
    Code:
    (employee) -> (name)
    In fact, an employee might even be a customer or vice-versa. What we have here are different roles for people, not different kinds of things. Let's formalize that:
    Code:
    (person) -> (name)
    customer: person
    employee: person
    Those last two are subtypes that identify persons in different roles.

    The spreadsheet contains two shifts: AM and PM. Let's assume they're not likely to change, and use them as-is for identifiers:
    Code:
    shift: {AM, PM}
    Finally, dates are a built-in data type in most DBMSs, so we don't need to model the domain explicitly. Whether a date is a weekend can be computed, so (date) -> (is_weekend) need not be recorded. However, we do need to record holidays, since some are arbitrary or difficult to compute.
    Code:
    holiday: date
    (holiday) -> (holiday_name)
    So, to summarize, our logical model is:
    Code:
    (employee, date, shift) -> (customer, work type)
    (person) -> (name)
    customer: person
    employee: person
    (work_type) -> (color, work_description)
    shift: {AM, PM}
    holiday: date
    (holiday) -> (holiday_name)
    Next, we need a physical schema to represent these:
    Code:
    schedule: employee_person_id PK/FK, date PK, shift PK/FK, customer_person_id FK, work_type_id FK
    people: person_id PK, name
    customers: person_id PK/FK
    employees: person_id PK/FK
    work_types: work_type_id PK, color, work_description
    shifts: shift PK
    holidays: holiday PK, holiday_name
    Then you need to select suitable data types to represent the domains. The id's would typically be INTs or GUIDs, date and holiday are DATEs, shift could be a CHAR(2), names and descriptions VARCHARs, and color an INT.

    BTW, I didn't include a mutual exclusion to prevent employees from working on holidays. If you need that, it can usually be implemented via a suitable check constraint or trigger.
    Last edited by reaanb; 06-03-15 at 21:57.

  4. #4
    Join Date
    May 2015
    Posts
    3
    Thank you very much for the effort you have put into this, its much appreciated.

    Regards,
    Oliver

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    I have to tell you, designing an application database that uses calendaring feature is NOT an easy task. It is expert-level database design and programming.
    You would be better off purchasing or licensing at cloud-based appointment management solution, and finding a simpler task specific to your line of business to cut your teeth on database application design.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    May 2015
    Posts
    3

    A little stuck

    OK, so I think I'm stuck...

    I have one table named employees, where I have columns for:
    PK EmployeeID
    FirstName
    LastName

    I have a second table with the calendar information eg:
    PK FullDate
    Year
    Month
    Day
    KindofDay (Weekday, weekend, Bank holiday etc)

    Now for where I'm a little confused - I think I need a third table that links the two above, so I have a table with something like:

    TableId
    Date - linked from calendar table
    EmployeeID - linked from employee table
    FirstName - linked from employee table
    LastName - linked from employee table
    Shift1Text
    Shift1Colour
    Shift2Text
    Shift2Colour

    If my logic is correct, I can then fill and get data from this table based on the Date and EmployeeID, so I would end up with an output along the lines of:

    Table ID | Date | EmployeeID | FirstName | LastName | Shift1Text | Shift1Colour | Shift2Text | Shift2Colour

    1 | 22/06/2015 | 1 | Joe | Bloggs | SomeText | SomeColour | SomeText | SomeColour
    2 | 22/06/2015 | 2 | Fred | Bloggs | SomeText | SomeColour | SomeText | SomeColour
    3 | 22/06/2015 | 3 | Bob | Bloggs | SomeText | SomeColour | SomeText | SomeColour
    4 | 23/06/2015 | 1 | Joe | Bloggs | SomeText | SomeColour | SomeText | SomeColour
    5 | 23/06/2015 | 2 | Fred | Bloggs | SomeText | SomeColour | SomeText | SomeColour
    and so on...

    The problem is, I have no idea how to go about linking the tables in this way, or populating the rows with the date and employee info

    Any help much is appreciated.

    Regards,
    Oliver

  7. #7
    Join Date
    Feb 2012
    Posts
    76
    Relational databases work on matching values, there is nothing to link. Foreign key constraints can be used to enforce integrity, e.g. to prevent calendar entries for non-existent employees. To populate your tables, just insert rows with the right values.

Posting Permissions

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