Creating a structure to allow for optional teams of people rather than just individual people

Question

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.

More detailed specs

As requested by SQLRaptor, here are some more specs:

Developers

• A Developer is a Person which is identified by developer_id.
• A Developer contributes to zero or more projects
• A Developer may sign up as an individual developer or may be employed by a company. (I see this as a problem source)
• A developer may be assigned to zero or more teams

Companies

• A Company is a legal identity which is identified by company_id.
• A Company hires teams of one or more Developers.
• A Company owns Projects
• Developers may be employed by Companies or may be signed up as an individual developer (again this is a problem source)

Teams

• A Team is a logical container for Developers which is identified by team_id
• A Team can be assigned to zero or more Developers.
• A Team can be assigned to zero or more Projects.
• A Team belongs to a Company.

Projects

• A Project is a collection of tasks identified by id.
• A Project may be assigned to zero or more Teams
• A Project belongs to either a Company or individual Developer. (This is a problem source)

Questions in regards to the problem sources:

• Should individual developers be required to sign up as a Company?
• Should individual developers be assigned a default Team? (In the real world, Teams only exist when there is a Company)?

If you have any other questions please ask.

Answer 1

Am I on the correct track of thinking here?

IMHO you are not. You are putting the carriage in front of the horse. To get back on the right track, start at the beginning, with your data universe. Let's list what entities you have in your game, and separate that from the business rules. First question is: "what is it in the real world, which we are trying to model?". From what you said, I see the following - please fill in the missing pieces. the essence and relationships between developers - companies - teams - projects is not well defined IMHO.

1. Developers

   A Developer is a Person which is identified by {Fill in} A Developer contributes work to Projects A Developer works for a company {True?} A Developer IS a part of a team A Developer may be assigned to zero or more teams {True?}

   ** You said that the developer 'may be part of a team', but it doesn't work that way. you can't have the cake and leave it whole. If your universe has a hierarchy of teams now, it is true for all. The way you keep 'the old way', is by creating a team of one person. This will resolve the evil cyclic reference that you can see in the solution that Alfonso suggested, who was just following your specs.

   

2. Companies

   A Company is a legal entity which is identified by {Fill in}. A Company hires teams of one or more Developers. A Company owns Projects {True?}. Developers are employed by Companies {True?}.

3. Teams

   A Team is a logical container for developers which is identified by name. A Team can be assigned zero or more developers. A Team can be assigned to zero or more projects {True?}. A Team belongs to a company {True?}.

4. Projects

   A Project is a collection of tasks which is identified by name. A Project may be assigned to zero or more teams. A Project belongs to a company {True?}.

Once you fill in the gaps and clarify what you have, we can continue on the right track.

---

-- Follow up to OP Updating Requirements --

---

Developers are not identified by ID. That is a surrogate key which does not exist in the real world. We will sort out the table keys later. The question is "How do you tell developers apart in the real world". I would assume they have a unique user name, or use their Email address to log on, so that your application can identify them, right? perhaps you use SSN? A company, team, and projects are probably identified by their name, which is what tells them apart in the real world, unique in their respective scope of course - It seems that team per company, and project per company make sense.

As for the entities, based on your reply I now believe that we have missed a few. Let me suggest something, and see if that works for you. I think the term 'Companies' is misleading, as you don't care if it's a company or an individual. What this represents is your customers, or the project owners, which are the legal entity which you issue invoices for. Since you already mentioned the separation of roles (developer / administrator / Read only etc.), I've taken the liberty of adding a couple of more entities that exist out there, and which you probably want to consider in your model. Let me know if this makes any sense.

Customer:       A Customer is a legal entity identified by it's tax identification number (SSN/ITIN). 
                Customers employ zero or more Employees.
                Customers own zero or more Projects.

Employee:       An Employee is a Person, identified by his (SSN / User Name / Email Address).
                Employees are employed by a Customer.
                Employees can have zero or more roles in association with zero or more Teams.

Role:           A role is a description of responsibility area for an Employee.
                Roles are identified by name.
                Roles can be one of : Developer, Project Viewer, Project Manager, Adsministrator, Team Lead)
                An Employee may have zero or more roles.

Team:           A Team is a collection of zero or more Team Members. 
                A Team is identified by name.
                A Team may be associated with zero or more projects.

Team Member:    A Team Member is an Employee that is associated with a Team.
                A Team may consist of Employees from one customer only.
                Team Members have a single role in a Team.

Developer:      A Developer is an Employee.
                A Developer contributes work for projects.
                A Developer can be associated with zero or more teams.

Project Viewer: A Project Viewer is an Employee.
                A Project Viewer may access Projects, but is not allowed to make changes.               

Project Manager:    A Project Manager is an Employee.
                    A Project Manager manages zero or more Projects, and the Teams associated with them.

Administrator:  An administrator is an Employee.
                An administrator represents the Customer and has permissions to manage Employees, Roles, Teams, and Projects.

Team Lead:      A Team Lead is a developer.
                A Team Lead manages zero or more teams.
                A Team may have only one Team Lead.

Project:        A Project is a collection of tasks, which are to be executed by Teams.
                A Project belongs to one Customer.
                A Project is identified by name.
                A Project may be assigned to one or more Teams.

Answer 2

You should remove the developer_id from the Project table because you already defined a many-to-many relationship between Developer and Project (the ProjectDeveloper table); this would produce data inconsistency.

However, could you give more information about your modelling, like a brief documentation? This will help us to check detailed your tables and their relationships.

EDIT

Thanks for adding more information! Please, check below my design.

There's two points regard to the design above:

• As a company may have an account for manage all his teams, then an accounts table needs to be created: TeamAccounts. Notice that there's a relationship one-to-one between Companies and TeamAccounts (enforced by an unique constraint at TeamAccounts table) because a company could have only an account to manage his teams.

• Three tables were added to team management, such as add a developer to a team, and so on. These tables are: Staffs, Permissions, and StaffsPermissions. Staffs will store Company's employees, Permissions will store all actions related to team management that an user could do, and StaffsPermissions will store all actions that a staff could do. This approach gives to you flexibility to user's permissions, because an user A may have two permissions, whereas user B may have three permissions.

You can see the diagram above just by clicking here.

I hope this may help you. However, if you got questions about this, don't doubt to ask it!