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?
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:
- 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
- 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)
- 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.
- 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.