Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2007
    Posts
    4

    Need some help with database design connections.

    Hi, first post here.

    Need some help regarding my database design. Its not so easy to explain in one post, but I'll try. (Besides my English is poor.)

    I will make a system for recording of employees and their schedule of work. I wonder if I'm on the right track here. The thing Im mostly unsure about now is the Schedule table.. Should I connect it to the Exceptions some how instead? I do need to

    check if there are any Exceptions on one particular day in the schedule table, if there is, it should say so in the report, otherwise just print the value from the Schedule table..


    Reports:
    Total work time for each employee from the schedule table including exceptions from the exceptions table, per month.
    Total statistics of the different exceptions, (sickness, vacation, over time..and so on.)
    What Competences does each employee have?

    Useability:
    Personal sign/approval on each exception by any supervisor.
    Check the Employee list, and if the employee is hired by our - Is he available for work today, or already called in?


    If some one could take the time, I would be greatful..
    Attached Thumbnails Attached Thumbnails upload.gif  
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the Employee columns fkeyPhoneNumbers and fkeyCompetences are wrong and should be removed

    unless you need to know very often which employees share a phone number, the PhoneNumbers table is wrong and should be removed

    the table ExceptionTypes is wrong and should be removed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2007
    Posts
    4
    Quote Originally Posted by r937
    the Employee columns fkeyPhoneNumbers and fkeyCompetences are wrong and should be removed

    unless you need to know very often which employees share a phone number, the PhoneNumbers table is wrong and should be removed

    the table ExceptionTypes is wrong and should be removed
    Ok, tnx for your time,

    About the PhoneNumbers table.. I need to add 1 or many phone numbers for 1 employee, how do I manage that?
    And every Employee can have 1 or more from a list of competences, and that list must be able to get updated by some admin..
    I'll stay there for now, and leave the exceptions problem for later..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, sorry about the phone numbers table, yes, you are right, it requires a one-to-many relationship

    i would use the actual phone number as the key, though

    as for the many-to-many EmployeesComptences table, that is also correct, but "fkeyCompetences" in the Employees table is wrong
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    4
    Quote Originally Posted by r937
    i would use the actual phone number as the key, though
    Well, that could work, if there are no couples in the company ..
    Quote Originally Posted by r937
    "fkeyCompetences" in the Employees table is wrong
    Ok, any suggestion? What about from pkeyEmployeeID instead?

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    from pkeyEmployeeID instead?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Realistically, how many phone numbers can any one employee have?
    I know it's not best design practice but if the most an employee could have is 2 then why not just have
    Employees(EmployeeID, Name, Telephone, otherTelephone, ... )
    George
    Home | Blog

  8. #8
    Join Date
    Jul 2007
    Posts
    4
    Quote Originally Posted by georgev
    Realistically, how many phone numbers can any one employee have?
    I know it's not best design practice but if the most an employee could have is 2 then why not just have
    Employees(EmployeeID, Name, Telephone, otherTelephone, ... )
    Ok, I will see how I do that, it's not the biggest of my problems.

    What I don't know is how to solve my relations between Exceptions-Employee-Schedule.

    Each Employee has got a "fixed" (although editable) schedule. And when there occur some exception (sickness, overtime...) beside the schedule this should be recorded. That's where I'm stuck now.. Some point in right direction?
    Last edited by pagehunter; 07-30-07 at 15:17.

Posting Permissions

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