• At first sight, the data model looks okay. Of course I do not know the exact requirements. Also, every flavor of ER modeling has its own ways to represent optional/mandatory and one/many in relationship ends, so I didn't try to read anything in the circles and dashed lines. (I think the craw's feet meaning is quite standard in the representations that use them, though)

    Based on your written explanation in your post, all relationships are optional. So you can have teams without member roles that are not part of a department, departments with no teams and no member roles, and member roles that are not part of either a team or a department. If any of these statements is incorrect, then you may have to make some of the relationships mandatory instead of optional. (Or perhaps you need some special constraints, for instance if a department must have at least one member role or one team - in that case each relationship in itself is optional, but the combination is mandatory).

    (Note that most database engines cannot enforce a mandatory relation if it's many to many, unless it's an RDBMS that supports deferred constraint checking - which SQL Server does not, unfortunately)

    I think that the choice to make all three relationships optional is okay. But do check. Can a department have multiple teams and/or member roles? Can a team be part of multiple departments and have multiple roles? Can a member role belong to multiple teams and/or departments?

    You might also want to checkk for mutually exclusiveness between the relations - i.e. if a department has a team, can it also have direct member roles, or is is either or? Same again for all other entities and relationships.

    In a pure relational design, you should not store the implied department-role membership, since this is redundant data that can always be inferred from the dept-team and team-role relations that are stored. However, if this poses a performance problem you may need to denormalize here. If you do, then my first choice in SQL Server would be an indexed view; failing that I would either add a fourth relationship table for only the implied relationships or add them to the existing table with an additional attribute to distinguish the implied from the direct connections.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/