Results 1 to 4 of 4

Thread: Design question

  1. #1
    Join Date
    Apr 2010
    Posts
    2

    Unanswered: Design question

    Hey everyone,

    I've done some real basic DB work in mySQL. Now, however, I'm working on a project at work using MS SQL and is a little more complex than I've worked with before. So, I guess my message here is I'm pretty new yet.

    My question is about design and the best way to layout some tables. I'm working on a online phone directory for 2 hospitals. Here is what I have now

    Depart_tbl
    Depart_Name
    Depart_Phone
    Depart_ID (PK)
    Depart_Fax
    Depart_Facility

    Emp_tbl
    FirstName
    LastName
    Title
    Emp_Phone
    Emp_Pager
    Emp_Cell
    Emp_Fax
    ID(PK)
    Emp_Facility
    Depart_ID(FK with Depart_tbl.Depart_ID)

    This works fine if the employee is a part of only 1 department, however, I'm now finding that some are a part of 2 departments.

    What is the best way to setup the tables to allow the employee's to be part of 2 separate departments?

    If the description is too vague, please let me know and I'll provide whatever details are needed.

    Thanks for the help/suggestions!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You need to establish a "many-to-many" relationship between employees and departments using a third table. The natural, unique key of this table ("EmployeeDepartments") would be the primary keys from Employees and Departments.

    Any reason you are choosing to mix two distinct naming conventions in your design? That's bound to prove annoying at some point.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2010
    Posts
    2
    Well I've never used a many to many relationship, although I know a little about them.

    As far as the naming convention, I will be recreating the tables anyway, so I'll look closer at staying with one format.

    Thank you very much for the help. If you have any good links for details on many to many, I'd appreciate it, if not Google is my friend.

    Thanks again!

  4. #4
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    You might, also, consider an employee phone table (emp_id, phone_type (i.e.ager mobile, etc...) phone_num (possibly split up by area code/prefix, etc...)), same for the department. As an employee may or may not have a pager AND a mobile, or none of them, could even have multiple land lines to one employee.
    Dave

Posting Permissions

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