normalization questions

My disclaimer:
I am by no means a design expert. My goal here is to learn, not criticize.
With that out of the way... Im confused. I was just reading this article:
http://www.sqlservercentral.com/columnists/bkelley/normalization.asp
Thank you BKelly, and again Im just trying to understand here. I have
several questions about the end result.
1; If I want to query to see all football players, I have to query Sports,
Team, Contract, and Clients. Mostly on character data types. Would it make
more sense to have a lookup table as in my DDL below. This would probably be
a very common query, and it seems that this would be a faster way.
2; What if an Agent wants to represent more than 1 Sport?
3; What if a Sport has more than 1 Agent?
4; Isnt LName, FName in the same column a violation of something?
5; In my design, Sport can be type-oed in the Team table. Would it be better
for me to have a Sport table with a SportID column, and relationships on that
column?
6; Again in my design, if I want to see all the Teams an Agent represents
(probably a common query), I need to go through several tables. Would I be
better off to have a lookup table between them?
7; Are most of these questions really just dependant on the requirements,
which obviously we dont know all of?
8; Am I just way out in left field with my design?
What I did here was designed the way I think it should be, and am hoping to
get some constructive criticism as well as answerrs to the above questions.
All points, good and bad, are welcomed. DDL below. Again, to learn here is my
only aim.
BE NICE JOE CELKO!
use [SportsAgency]
GO
CREATE TABLE [dbo].[Agent] (
[AgentId] [int] IDENTITY (1, 1) NOT NULL ,
[AgentLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[AgentFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Client] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientLName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[ClientFName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientAgent] (
[ClientID] [int] NOT NULL ,
[AgentId] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[LKPClientTeam] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Salary] (
[ClientID] [int] NOT NULL ,
[TeamID] [int] NOT NULL ,
[YearlySalary] [money] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Team] (
[TeamID] [int] IDENTITY (1, 1) NOT NULL ,
[TeamName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Sport] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Agent] WITH NOCHECK ADD
CONSTRAINT [PK_Agent] PRIMARY KEY CLUSTERED
(
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Client] WITH NOCHECK ADD
CONSTRAINT [PK_Client] PRIMARY KEY CLUSTERED
(
[ClientID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientAgent] PRIMARY KEY CLUSTERED
(
[ClientID],
[AgentId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientTeam] WITH NOCHECK ADD
CONSTRAINT [PK_LKPClientTeam] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Salary] WITH NOCHECK ADD
CONSTRAINT [PK_Salary] PRIMARY KEY CLUSTERED
(
[ClientID],
[TeamID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Team] WITH NOCHECK ADD
CONSTRAINT [PK_Team] PRIMARY KEY CLUSTERED
(
[TeamID]
) ON [PRIMARY]
GO
CREATE INDEX [IX_Client] ON [dbo].[Client]([ClientID]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[LKPClientAgent] ADD
CONSTRAINT [FK_LKPClientAgent_Agent] FOREIGN KEY
(
[AgentId]
) REFERENCES [dbo].[Agent] (
[AgentId]
),
CONSTRAINT [FK_LKPClientAgent_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
)
GO
ALTER TABLE [dbo].[LKPClientTeam] ADD
CONSTRAINT [FK_LKPClientTeam_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_LKPClientTeam_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
ALTER TABLE [dbo].[Salary] ADD
CONSTRAINT [FK_Salary_Client] FOREIGN KEY
(
[ClientID]
) REFERENCES [dbo].[Client] (
[ClientID]
),
CONSTRAINT [FK_Salary_Team] FOREIGN KEY
(
[TeamID]
) REFERENCES [dbo].[Team] (
[TeamID]
)
GO
--
TIA,
ChrisRComments inline...
--
David Portas
SQL Server MVP
--
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:3BC29E40-C32D-44CE-8C0C-4778156B27DF@.microsoft.com...
> My disclaimer:
> I am by no means a design expert. My goal here is to learn, not criticize.
> With that out of the way... Im confused. I was just reading this article:
> http://www.sqlservercentral.com/columnists/bkelley/normalization.asp
> Thank you BKelly, and again Im just trying to understand here. I have
> several questions about the end result.
> 1; If I want to query to see all football players, I have to query Sports,
> Team, Contract, and Clients. Mostly on character data types. Would it make
> more sense to have a lookup table as in my DDL below. This would probably
> be
> a very common query, and it seems that this would be a faster way.
Perhaps. But you've missed out some vital constraints. For example your
design allows the same agent to be entered multiple times. IDENTITY is not
an integrity constraint so it shouldn't be the only key of any table. BTW I
try to avoid the term "lookup table" when discussing logical design. I think
that term misleads the inexperienced into thinking that those tables are
somehow inherently different to others. Logically, there is only one type of
table in an RDBMS.
> 2; What if an Agent wants to represent more than 1 Sport?
> 3; What if a Sport has more than 1 Agent?
Then you have a many-to-many relationship. To implement that use a "joining
table" that contains the keys from both Agent and Sport.
> 4; Isnt LName, FName in the same column a violation of something?
Not formally a violation of NF, no. But if you need to represent that
element of data somewhere as two separate values then it probably makes
sense to define them as separate attributes.
> 5; In my design, Sport can be type-oed in the Team table. Would it be
> better
> for me to have a Sport table with a SportID column, and relationships on
> that
> column?
It would be better to create a *constraint* to enforce the business rule
that only valid sports can be entered. That could be a check constraint or
more likely a foreign key constraint to a separate Sport table. Creating a
"sportid" as a surrogate key is irrelevant to enforcing the business rule -
it's purely a physical model issue.
> 6; Again in my design, if I want to see all the Teams an Agent represents
> (probably a common query), I need to go through several tables. Would I be
> better off to have a lookup table between them?
Don't see how it would help the query but it's probably a better design
because of 2 and 3 above.
> 7; Are most of these questions really just dependant on the requirements,
> which obviously we dont know all of?
YES
> 8; Am I just way out in left field with my design?
>
You are taking the wrong approach to learning this. Get a book and learn the
basics of the Relational Model first. Then you will be ready to start
applying your knowledge to SQL tables. Otherwise you are like a
mathematician trying to teach yourself algebra by playing with a pocket
calculator.
> What I did here was designed the way I think it should be, and am hoping
> to
> get some constructive criticism as well as answerrs to the above
> questions.
> All points, good and bad, are welcomed. DDL below. Again, to learn here is
> my
> only aim.
> BE NICE JOE CELKO!
>
>