Schema Design Question - Teams

Question

Schema Design Question - Teams

BSavoie

SSCertifiable

Points: 6309
More actions
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
.

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

BSavoie SSCertifiable Points: 6309 More actions · Answer 2

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.

.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 3

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.

BSavoie SSCertifiable Points: 6309 More actions · Answer 4

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?

.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 5

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.

BSavoie SSCertifiable Points: 6309 More actions · Answer 6

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.

.

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 7

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.

Paul White SSC Guru Points: 150467 More actions · Answer 8

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