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

database-designMySQL

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.

Best Answer

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.

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.
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?}.
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?}.
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.

Related Solutions

MySQL – Fix Extremely Long Query Time with Nested WHEN-INs

Let's start with your original query

   SELECT *  
    FROM reports 
    WHERE report_name IN ( 
        SELECT report_name  
        FROM reports  
        WHERE region IN ( 
            SELECT distinct region  
            FROM macroregions 
            WHERE macroregion IN ( 
                SELECT distinct macroregion 
                FROM users  
                WHERE callsign = '$thisuser' 
            ) 
        )     
    )

You can gather keys only (in stages), then join the keys with the reports table. Here is my new proposed query

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        USING (macroregion)
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    USING (region)
) reportnamekeys
INNER JOIN reports
USING (report_name);

If you are not comfortable with the USING clause here is my new propsed query without using the USING clause:

SELECT reports.* FROM
(
    SELECT rpts.report_name FROM
    (
        SELECT DISTINCT regions.region FROM
        (SELECT DISTINCT macroregion
        FROM users WHERE callsign = '$thisuser') users
        INNER JOIN
        (SELECT DISTINCT macroregion,region FROM regions) regions
        ON users.macroregion = regions.macroregion
    ) regionkeys
    INNER JOIN
    (SELECT region,report_name FROM reports) rpts
    ON regionkeys.region = rpts.region
) reportnamekeys
INNER JOIN reports
ON reportkeys.report_name = reports.report_name;

You will need some indexes to support the subqueries

ALTER TABLE users   ADD INDEX callsign_macroregion_ndx (callsign,macroregion);
ALTER TABLE regions ADD INDEX macroregion_region_ndx (macroregion,region);
ALTER TABLE reports ADD INDEX region_report_name_ndx (region,report_name);
ALTER TABLE reports ADD INDEX report_name_ndx (report_name);

The first 3 indexes are called covering indexes. They are called such because the subqueries calls for only those exact columns. Therefore, no need to read from the table. The data is only fetched from the index.

You should also study how to refactor queries to gather keys, perform WHERE clauses early, and perform JOINs last. Here is great YouTube Video on how to do this: http://youtu.be/ZVisY-fEoMw (Based on this book : Refactoring SQL Applications)

Give it a Try !!!

Relational database: in-RAM partitioning? Theoretical structure discussion

That's a long question.

First off, my current project (I'm the database guy, there are MMO engine experts to deal with that) is a form of MMORPG based on an off-the-shelf engine. Volumes would be like Eve Online" or "World of Tanks" volumes.

Now for an orthogonal short answer:

separate DB and Engine completely
Don't mix and match because of hardware optimisations
hardware: DB and engine servers will be way different specs
design your database normally

There is a whole lot more of course, but I'd suggest you're over-thinking the problem and shooting yourself in the foot. I'm simply applying the same techniques to my MMO that I used in Investment Banking because IMO most high volume systems should converge to the similar architecture

Best Answer

Related Solutions

MySQL – Fix Extremely Long Query Time with Nested WHEN-INs

Relational database: in-RAM partitioning? Theoretical structure discussion

Related Question