Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    27

    Unanswered: Many-to-Many on Separate FKs

    I have an employee table and a project table. Projects table has fields for Salesperson and ProjectLeader that both link to the employee table. In a way, that is many to many.

    Should I create a junction table instead?

    There are a few more similar fields I will add, and some might not have an employee (Salesperson might be null if it is repeat business).

    Thank you!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what happens if more than one employee is on a single project?

    what you have is not a proper many-to-many relationship

    yes, use a junction table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2011
    Posts
    27
    The employee and project many-to-many is exactly my concern. Just to make sure I understand correctly, did you mean to type "not a proper one-to-many relationship"? I wasn't sure if it was a typo or if there is ever a proper m-to-m. I'm pretty sure that's what you meant, regardless, I'll start a junction table!

    Quote Originally Posted by r937 View Post
    what happens if more than one employee is on a single project?

    what you have is not a proper many-to-many relationship

    yes, use a junction table

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I believe r937 did mean Many-To-Many. I believe his point was that a project will typically have more than one person working on a project. And, I think the assumption was, one employee will work on more than one project at a time.

    So you really should have a third table (ProjectTeam???) that stores the people that are working on the project. However, your schema may work for you if your business requires you to only have one Project Leader. If, for some reason, there is a very large project and for this ONE project there are two leads, your database won't be able to handle it.

Tags for this Thread

Posting Permissions

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