Post reply

Schema Design Question - Teams

  • December 30, 2011 at 9:42 pm

    #248288

    I have an app where I need to design tables around the concept of a "team". I'm not real sure how to handle this. Each team has exactly two members.

    My first thought is to have a "Teams" table with columns for teamId, member1, member2. Then, of course a "Members" table with memberId's.

    This seems like a pretty clear normalization violation, but it seems to exactly model the situation. First off, I don't think I could use DRI for this sort of thing, that's not a good sign! And I can imagine it would make for some ugly queries. But how else can I enforce each team having exactly two members?

    Any thoughts?

    Thanks as always

    .

  • December 30, 2011 at 9:53 pm

    #1427156

    The team table would not need to have any memberids. It should be the other way around - members table will have a teamid.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • December 30, 2011 at 11:07 pm

    #1427164

    Thanks SQLRNNR.

    That was definitely my first thought, but it doesn't really enforce the "two members per team" rule; whereas the other way around enforces it quite explicitly.

    Maybe I should go with your recommendation and enforce my rule with insert/update triggers.

    .

  • December 31, 2011 at 12:58 am

    #1427177

    BSavoie (12/30/2011)

    Thanks SQLRNNR.

    That was definitely my first thought, but it doesn't really enforce the "two members per team" rule; whereas the other way around enforces it quite explicitly.

    Maybe I should go with your recommendation and enforce my rule with insert/update triggers.

    If a member can belong to multiple teams you shouldn't do that. Even if it's not in the requirements I would like you to consider it as a requirement because business can change their operational policies anytime & your data model should be flexible to adopt that change.

    Consider it a Many-to-Many relationship & introduce a bridge / association table for it.

  • December 31, 2011 at 1:04 am

    #1427179

    Thanks Dev. I hear ya. I like that plan, but what would be the best way to enforce the the "two members per team" rule on the server side? Check for and prevent more than two members in a insert/update trigger?

    .

  • December 31, 2011 at 1:12 am

    #1427182

    BSavoie (12/31/2011)

    Thanks Dev. I hear ya. I like that plan, but what would be the best way to enforce the the "two members per team" rule on the server side? Check for and prevent more than two members in a insert/update trigger?

    You don’t need a trigger for it. The DMLs can be controlled by stored procedures. Put your business rules in them & force the DEV team to use SPs only for database operations.

  • December 31, 2011 at 1:20 am

    #1427184

    I appreciate the concept, and I can definitely go that direction, but forcing developers (of which I am one) to use the sprocs and not the tables directly can be a bit of a challenge at my place. There are many that have nearly sa privaleges, they can be a problem for these sorts of things. Not intentially mind you, it's just that in six months or a year when they go after this data again, the sprocs will be forgotten or ignored.

    Thanks though. It does seem like my best bet.

    .

  • December 31, 2011 at 1:30 am

    #1427186

    BSavoie (12/31/2011)

    I appreciate the concept, and I can definitely go that direction, but forcing developers (of which I am one) to use the sprocs and not the tables directly can be a bit of a challenge at my place. There are many that have nearly sa privaleges, they can be a problem for these sorts of things.

    You are thinking only from development point-of-view. In PROD environment it would be controlled (& should be controlled).

    Not intentially mind you, it's just that in six months or a year when they go after this data again, the sprocs will be forgotten or ignored.

    Then I have to say, the database development & administration in immature hands. Anything (table definitions, a check constraint or server configuration etc) that moves to PROD environment needs a proper documentation & version control.

  • December 31, 2011 at 4:12 am

    #1427198

    BSavoie (12/31/2011)

    I like that plan, but what would be the best way to enforce the the "two members per team" rule on the server side? Check for and prevent more than two members in a insert/update trigger?

    Given the lack of direct support for this sort of very useful constraint, my preference would be to use triggers, yes.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply