Basic Design Question
-
I'm trying to model the concept of "teams" of "players". Each team has exactly two players. Intuitively, it would seem a "Teams" table with Player1 and Player2 columns makes great sense. Of course this is a fundamental violation of normalization. Sometimes it makes sense to violate a rule here and there. Is this one those times or do I go with the standard Teams/Players/TeamPlayers solution? I'll also face this same problem with the schedule table. A schedule item schedules exactly two teams to play each other.
Thanks for your opinion and happy holidays.
.
-
Someone once said to me "normalize until it hurts, denormalize until it works."
The logic of this is that you should always properly normalize unless the fully normalized approach causes you some kind of problem (like performance) down the road.
So my advice is to go with the normalized version until something drives you to denormalize it.
My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St -
Good point dwain.
.
-
BSavoie (9/3/2012)
I'm trying to model the concept of "teams" of "players". Each team has exactly two players. Intuitively, it would seem a "Teams" table with Player1 and Player2 columns makes great sense. Of course this is a fundamental violation of normalization. Sometimes it makes sense to violate a rule here and there. Is this one those times or do I go with the standard Teams/Players/TeamPlayers solution? I'll also face this same problem with the schedule table. A schedule item schedules exactly two teams to play each other.Thanks for your opinion and happy holidays.
So.... what do you want to do about subs or players that are replaced? Ask yourself how your denormalized table would handle that.
My recommendation is that you save denormalization for reporting tables. For the backbone data, do it right. Normalize it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
You are absolutely right about those things Jeff, I've not been giving those the attention they deserve.
Thanks
.
-
You're welcome. Thank you for the feedback.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
+1 for the normalized approach. It may be 2 players today but it likely to be expanded upon at some point and you can have that addressed in your initial design.
Mark
-
I'm with Mark on that one. The client may be telling you that the requirement is always for two-person teams and that this will never change, but if you believe that you may be interested in this bridge I'm selling. 🙂 Requirements have a habit of changing, and since the normalised approach will seamlessly handle any number of players in teams and any number of teams playing each other, why not use it?
-
Yea I'm sold on that approach. I'm headed down that road now.
.
-
Without any prior experience of this type of data, I'd be tempted to add a time dimension. That would give you team history, the team as it is right now, and a calendar for future events.
Like this, perhaps:
CREATE TABLE TeamMembers (TeamMemberID INT IDENTITY (1,1), -- surrogate key; player may leave & rejoin
TeamID INT NOT NULL,
PlayerID INT NOT NULL,
Position VARCHAR(100) NULL,
DateJoined DATETIME NOT NULL DEFAULT GETDATE(),
DateLeft DATETIME NULL,
CONSTRAINT pk_TeamMembers PRIMARY KEY (TeamMemberID),
CONSTRAINT fk_Teams FOREIGN KEY (TeamID) REFERENCES Teams(TeamID),
CONSTRAINT fk_Players FOREIGN KEY (PlayerID) REFERENCES Players(PlayerID)
)
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
ChrisM@Work (9/4/2012)
Without any prior experience of this type of data, I'd be tempted to add a time dimension. That would give you team history, the team as it is right now, and a calendar for future events.Like this, perhaps:
CREATE TABLE TeamMembers (TeamMemberID INT IDENTITY (1,1), -- surrogate key; player may leave & rejoin
TeamID INT NOT NULL,
PlayerID INT NOT NULL,
Position VARCHAR(100) NULL,
DateJoined DATETIME NOT NULL DEFAULT GETDATE(),
DateLeft DATETIME NULL,
CONSTRAINT pk_TeamMembers PRIMARY KEY (TeamMemberID),
CONSTRAINT fk_Teams FOREIGN KEY (TeamID) REFERENCES Teams(TeamID),
CONSTRAINT fk_Players FOREIGN KEY (PlayerID) REFERENCES Players(PlayerID)
)
+1
Good point. Nicely raised - a player may leave the team and re-join.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply