#StackBounty: #mysql #database-design Creating a structure to allow for optional teams of people rather than just individual people

Bounty: 50

I currently have a database set up that has tables as follows:

Developer
    id (PK)

Project
    id (PK)
    developer_id (FK)

/* and a number of other first or second level tables related to the Project table. */

Currently each developer can have one or more projects in the project table.

I am now wanting to provide an option for having team management and a team of developers for companies that want to manage entire teams. However, still want the original method of doing things if teams are not needed.

So far I have come up with the idea to implement things like this:

Developer
    id (PK)

Company
    id (PK)
    name

CompanyDeveloper
    company_id (FK)
    developer_id (FK)
    manage_teams (bool) - allows user to create, delete, and manage teams and team members

Team
    id (PK)
    company_id (FK)
    name

TeamDeveloper
    team_id (FK)
    developer_id (FK)

ProjectDeveloper
    project_id (FK)
    developer_id (FK)
    view - allows the developer to view a particular project
    edit - allows the developer to edit a particular project

/* Modified project table to add team_id */
Project
    id (PK)
    developer_id (FK)
    team_id (FK) - can be null for those developers working by themselves and not in a team for a particular company.

Am I on the correct track of thinking here? Is there anything that I am doing wrong or that I could implement in a better way?

Requirements

As requested, my requirements are as follows:

  • Allowing individual developers to develop one or more projects (this is what I currently have in place)
  • Companies may purchase a team account in order to manage teams of developers.
  • One or more people in the company are able to manage teams which includes creation of teams, adding developers to teams, and assigning teams to projects.
  • Some team developers are able to view the project only, whereas others are able to edit them.

If you have any other questions please ask.


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.